Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Friday, 30 November 2018

How to silently install SQL Server 2016 Standard

If you need to quickly install SQL Server with many of the default, the following command can be used. This is useful if you are like me and often have to build up lab environments to test stuff.

.\setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQL /INSTANCENAME=LAB /SQLSVCACCOUNT="LAB\SQLService" /SQLSVCPASSWORD="AppleP13"   /AGTSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="LAB\Domain Administrators"


Instance Name = LAB
Domain = LAB.local
SQL Server = LAB\SQLService

Wednesday, 28 November 2018

TDE encrypted database won't mount on SQL Server Standard after license upgrade, decrypt TDE database and migrate to SQL Server Standard


This is a continuation to my post on the TechNet forum, it’s effectively my notes on how to resolve the problem I initially asked to the community.


I had a SQL Server deployed in production which was installed using evaluation licenses. This was because at the time of install the volume license agreements were not signed off. As the evaluation licenses were due to expire I looked at the process of activating SQL Server with the customer’s volume license keys. If you have ever done this, you will know the process is basically to use the volume license media to upgrade the evaluation installation to the edition your volume license covers.
SQL Server, when installed in evaluation mode makes all the features of SQL Server Enterprise available. This caused a problem as one of the production databases had been encrypted with TDE.

TDE is a SQL Server feature only available in Enterprise edition. Once the license upgrade to Standard was complete the TDE encrypted database refused to mount as the feature was no longer licensed.

The process to resolve this is as follows (at a high level)
  • ·         Install new evaluation instance of SQL Server (which will give Enterprise).
  • ·         Export the original encryption key from the source instance.
  • ·         Create the new master key on the new instance.
  • ·         Import the original encryption key into the new instance.
  • ·         Copy the original database file and log to a new location.
  • ·         Attach the original database file and log to the new instance.
  • ·         Reconfigure application to utilise new SQL Server instance.

The above steps resolve the issue and will get the database back online. However, unless you want to run on unlicensed software, or pay to upgrade to SQL Server Enterprise, you will have to get the database running on the production SQL Server Standard instance. To do this the database will need decrypted, backed up and restored in order to remove the TDE encryption.

USE master
GO
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

You should see the certificate used to encrypt the database listed below. In my case it was called “TDECert”, this is needed for the next command.



Use the following command to export the encryption key with the private key. In the BACKUP CERTIFICATE statement remember and list the name of your certificate which is in the output above.

USE master;
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Users\ryan.betts\Desktop\Cert Export\Cert.cer'                                                         
WITH PRIVATE KEY (file='C:\Users\ryan.betts\Desktop\Cert Export\CertPRVT.pvk',
ENCRYPTION BY PASSWORD='SecretPassword');

Once the query has successfully completed the folder will be populated with the certificate and corresponding private key. 


Install a new SQL Server instance, I have selected Developer. The Developer edition of SQL Server is for testing purpose, but has all the features of SQL Server Enterprise.


To ensure your database mounts on the new temporary instance, ensure you install the instance with the correct database collation. I don’t believe the collation can be changed without reinstalling the instance.


 Once you have a new instance of SQL Server installed, a new Master Database Key must be created. This can be done using the following code, this should be run as a query from the new instance.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='SecretPassword';
GO


Once you have the new SQL instance installed, you must use the following command to import the certificate and private key which was used to encrypt the TDE database.

USE master;
GO
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\Users\da.ryan.betts\Desktop\Cert Export\Cert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\Users\da.ryan.betts\Desktop\Cert Export\CertPRVT.pvk',
 DECRYPTION BY PASSWORD = 'SecretPassword'
  );
GO

Now copy the original database and log files to a new location. This will protect the originals in case you need to revert back to them.

Open SQL Server Management Studio, expand the instance, then expand Databases. Right click and select Attach. 


Click Add and browse for the MDF file which was copied to the new location. If the log file is available, it should automatically detect that. Click Ok.


If all has gone well the database should now mount. 


If you have moved the database to a new SQL Server instance, your application front end will need reconfigured to point to the new instance name. Although all applications are different this is typically done with an ODBC connector in Windows.

The instance name comes after the server name for example SQLServer01\Instance01.

Now that the database is mounted and accessible, we can disable the TDE encryption and begin the database encryption process. The following command can be used to do this.

USE master;
GO
ALTER DATABASE dbname SET ENCRYPTION OFF;
GO
USE dbname;
GO
DROP DATABASE ENCRYPTION KEY;
GO

Once this has completed, you can check its worked by right clicking on the database and selecting Tasks > Manage Database Encryption. If the database is decrypted the Set Database Encryption On option should be unticked. 



Now run a standard SQL backup job, copy the backup file to the original production instance and perform a restore. Reconfigure the application to point to the original instance and you have now disabled TDE and the database will run on SQL Server Standard.

Wednesday, 4 April 2018

Backup SQL Server 2016 Databases using Powershell

It is possible with SQL Server 2016 (possibly earlier as well) to use Powershell to backup your databases. If I ever need to do this I use the SQL Management console, however I'm using a Mac through RDP which means right click is not working :)

It's very easy to take the backup, set the variables, of server name, db name and output path.

$server = “TWSQL1”
$database = “ConfigMgr”
$output = “C:\Users\Administrator\Desktop\SQL\ConfigMgr.bat”

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $output

Thursday, 27 November 2014

Forefront Identity Manager 2010 R2 SP1 (FIM): Connection to SQL Server 2008 R2 for FIM Sync Service "Connection Failed: SQL State: '08001' SQL Server Error: 5 Could Not open a Connection to SQL Server [5]. Connection Failed: SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login Timeout Expired"

Recently I have been building a FIM 2010 R2 SP1 Sync Service Server using an external SQL instance running on SQL Server 2008 R2. When I was creating the ODBC Data Source using the SQL Server Native Client 10.0 I receieved the following error "Connection Failed: SQL State: '08001' SQL Server Error: 5 Could Not open a Connection to SQL Server [5]. Connection Failed: SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login Timeout Expired".
 
 
The solution here was to enable TCP/IP from the SQL Server Configuration Manager to do this launch the SQL Server Configuration Manager MMC expand SQL Server Network Configuration and click Protocols for MSSQLSERVER right click and select Properties on the TCP/IP protocol. From the Properties window click IP Addresses using the Active drop downs select Yes for IP1 and IP2. Click OK.
 
For the changes to apply you must restart the SQL instance to do this click on SQL Server Services from the left hand list, right click SQL Server (MSSQLSERVER) and select Restart.
 
To finish off open the SQL Management Studio and login using Windows Authentication.

 
Right click on the top (local) database icon and select Properties.

 
Click on the Security tab and change the Server Authentication to SQL Server and Windows Authentication Mode. Click OK.
 
Again the SQL Server must be restarted. In this instance I rebooted the entire server.
 
Once this has been done you can continue with the ODBC Data Source Connector.

Tuesday, 25 November 2014

Forefront Identity Manager (FIM) Synchronization Service Evaluation is having trouble contacting SQL server using the provided information. Please note that Forefront Identity Manager Synchronization requires Microsoft SQL Server 2008 SP1 or better. Verify the version, server and instance names as well as firewall.

When you try to install the Forefront Identity Manager (FIM) 2010 R2 SP1 Synchronization Service to use an off-box SQL Server instance you receive the following error "Forefront Identity Manager Synchronization Service Evaluation is having trouble contacting SQL server using the provided information. Please note that Forefront Identity Manager Synchronization requires Microsoft SQL Server 2008 SP1 or better. Verify the version, server and instance names as well as firewall."
 
 
In this particular instance FIM 2010 R2 SP1 is running on Windows Server 2008 R2 and the SQL Server is Windows Server 2012 R2 with SQL 2012 Standard. As with all off-box SQL instance I had created a ODBC (64-bit) from the FIM server, to the SQL. When I tested connectivity it all came back as working.
As part of the troubleshooting I tried the following things without success
·      Connectivity and Name Resolution
o     Ping and Nslookup
·      SQL Services
o     Ensured SQL Server Browser and SQL Server Agent were set to Automatic and Running
·      Disabled the SQL Server Firewall
o     To rule out any issues with port 1433, then tried Telnet all OK

Although all of these tests came back positive the issue was not resolved. The issue was with the SQL ODBC connection from FIM to SQL. I had created the ODBC using the standard SQL Server Client build into Windows Server 2008 R2.
The fix was to install the SQL Server 2012 Native Client, which can be downloaded from Microsoft http://microsoft.com/en-gb/download/confirmation.aspx?id=29065




 
Once I recreated the ODBC connection with the SQL Server 2012 Native Client the setup then allowed me to continue, the next step was to configure the FIM security groups.