Sql Server Faq

Have you ever wondered how to change the keys for the encryption for an already encrypted database. For this you can use “Key Rotation”. Key rotation is the process of decrypting data with the old encryption key and encrypting the data with the new encryption key.

But how to use the Key Rotation with SQL Server 2005??

Below are specified a few details as to how you can go ahead and use the Key Rotation to change the key with which the data has been encrypted.

1. You can add an encryption by a temporary new certificate.
2. Drop the old encryption and the old certificate.
3. Create a new certificate with the old name, add an encryption by it.
4. Finally drop the encryption made with the temporary certificate and drop the temporary certificate as well.

And with this you have the data encrypted with a new key and the old key has been dropped.

You can refer to this blog by Laurentiu Cristofor of the Microsoft SQL Server Security team for more details and the alternatives as well.

 

 

We have a production database in which the data is encrypted. The encryption follows the following hierarchy

 

1) The data is encrypted using Symmetric key

2) The symmetric key was encrypted using asymmetric key

3) The asymmetric key was encrypted using the master key of the database

4) The master key of the database was encrypted using user defined password

 

 We created a MASTER KEY for the test database which encrypted using password

 

o    Use test

o    create master key encryption by password=’password’

 

·          We then created a asymmetric key which is encrypted by MASTER KEY of the database

 

o    Use test

o    open master key decryption by password=’password’

o    create asymmetric key MSSQL with algorithm = RSA_2048

o    go

 

·          In a test database with data, we then create a  symmetric key which  is used to encrypt the data

 

o    Use test

o    create symmetric key MSSQLSYM WITH ALGORITHM = AES_256 encryption by asymmetric key MSSQL.

o    go

 

 

·          We can check the presence of the keys

 

o    Select * from sys.symmetric_keys

o    Select * from sys.asymmetric_keys

 

·           We took the backup of the production database test and wanted to restore the database in the test environment.

 

·         However we wanted to change the password with which the master key is encrypted on the test environment so that password of the original database is not available to the developer who use the database in test environment.

 

·          We then restored the backup of the database test on other instance for the first time. By creating a new database and not overwriting on the existing database

 

·          When we restore the backup in the new instance we want to regenerate the master key of the database so we fire the following commands

 

o    open master key decryption by password=’password’

o    alter master key regenerate with encryption by password = ‘password’

o    close master key

 

 

·          However when we take a backup again from the original server and restore it on the new server by overwriting on the existing database when we fire the same set commands to regenerate the master key we received the following commands

 

o    open master key decryption by password=’password’

o    alter master key regenerate with encryption by password = ‘password’

 

o    Msg 15320, Level 16, State 2, Line 1

o    An error occurred while decrypting asymmetric key ‘MSSQL’ that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

 

 

·          When we query sys.databases table in the source database and checked the column is_master_key_encrypted_by_server. We found that it was set 1 which means the Master key was encrypted using password along with the service master key of the sql server.

 

·          When we restored the database to the new server for the first time. When we checked the is_master_key_encrypted_by_server column from the sys.database we found the column value was 0 which indicates that MASTER KEY was encrypted only using password and not the SERVICE MASTER KEY of the sql server.

 

·          Therefore we regenerated the MASTER KEY it worked fine without any error. Also after regenerating the master key when we check the table sys.databases again we find that column is_master_key_encrypted_by_server is set to 1 which means that the regeneration encrypts the master key by password and the SERVICE MASTER KEY of the TEST sql server instance

 

·          However when we restore the database on the existing database by overwrting on it. The master key is encrypted using password and SERVICE MASTER KEY so the regeneration fails.

 

·         So in order to regenerate the master key of the database we first need to drop the encryption of the service master key using the following command

 

·          alter master key drop encryption by service master key

 

·          and after dropping the encryption of service master key explicitly when we regenerate the master key using the same command which is used by cx it works fine

 

·          alter master key regenerate with encryption by password = ‘password’

 

Kerberos is a network authentication protocol which can be used only with TCP/IP protocol. So if the client connects to the sql server with the Named Pipe Alias Kerberos is not used. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If an SPN is not set for a service, then clients will have no way of locating that service. Without properly setting the SPNs, Kerberos authentication is not possible.

For more information on SPNs, please refer to the below article,

http://msdn.microsoft.com/en-us/library/ms677949.aspx

 

To use Kerberos authentication, you must make sure that all the following conditions are true:

•	Both the server and the client computers must be members of the same Windows domain or members of trusted domains.
•	The server’s service principal name (SPN) must be registered in the Active Directory directory service.
•	The instance of SQL Server 2005 must enable the TCP/IP protocol.
•	The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client’s protocol order. Or you can add the prefix “tcp:” in the connection string to specify that the connection will use the TCP/IP protocol.

What is SPN?

Service Principal Name (SPN) is the identity of a service which is started with the particular Domain Account running in that domain. SPNs are registered at the Domain Controller (Active Directory).

 To register the SPN at the AD we need to provide the following information

1)       The service

2)       The Fully Qualified Domain Name (FQDN) of the system on which the service is installed

3)       The TCP/IP port no. on which the service is listening. (Since we can have multiple services of same type running on the same system but on the different host for e.g  sql server)

4)       The startup account with which the service is started

SPNs are used by the clients who are connecting to that sql server service using windows integrated authentication mechanism

 When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN in the following format when the instance starts:

MSSQLSvc/<FQDN>:<tcpport>

 

C:\Program Files\Resource Kit>setspn -L pariks-2003

Registered ServicePrincipalNames for CN= pariks -2003,OU=Workstations,OU=Machines,DC=testdc,DC=corporate,DC=sampledomain,DC=com:

MSSQLSvc/ pariks -2003.testdc.corporate.sampledomain.com:1393

SMTPSVC/ pariks -2003

SMTPSVC/ pariks -2003.testdc.corporate.sampledomain.com

HOST/ pariks -2003

HOST/ pariks -2003.testdc.corporate.sampledomain.com

 

You must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication. To register the SPN, you can use the SetSPN.exe tool that is included with the Microsoft Windows 2000 Server Resource Kit. This tool is also included with the Microsoft Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Microsoft Windows Server 2003 Service Pack 1 (SP1).

You can use a command that is similar to the following to register an SPN for an instance:

SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>

If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.

After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

If SQL Server is using Kerberos authentication, a character string that is listed as “KERBEROS” appears in the auth_scheme column in the result window

In order to verify that Kerberos is setup and working fine we can use KerbTray utility. Kerbtray utility can be downloaded from

http://www.microsoft.com/downloads/details.aspx?FamilyID=4e3a58be-29f6-49f6-85be-e866af8e7a88&DisplayLang=en

How to use KerbTray utility

1)       Install the KerbTray utility from the above link and install it in C:\Program Files\Resource Kit

2)       On Installing it a GREEN icon appears on the Task Bar on the right hand bottom of the screen

3)       Right Click on the icon and click purge tickets to purge all the tickets received by that machine

4)       Wait until the green icon turns yellow. When the icon turns yellow fire the following command from the cmd prompt

C:\Program Files\Resource Kit>net session /d

The command completed successfully.

 

5)       Now we can request the ticket from the AD using the following command from the cmd prompt

                 C:\Program Files\Resource Kit>set L   (Press Enter)

                 lib=C:\Program Files\SQLXML 4.0\bin\

                 LOGONSERVER=\\comp-dc-01

                C:\Program Files\Resource Kit>net use \\ comp-dc-01\IPC$

                 Local name

                 Remote name       \\ comp-dc-01\IPC$

                 Resource type     IPC

                 Status            OK

                 # Opens           0

                 # Connections     1

                 The command completed successfully.

 

**comp-dc-01 is the DC for the domain.

 

After firing the above command wait for some time and you will see that yellow icon of the KerbTray turning green which indicates that it has started received Tickets which can also be confirmed by right clicking on the icon and click on LIST TICKETS

909801 (http://support.microsoft.com/kb/909801/) How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005

**To enable Kerberos logging in the Windows Xp, 2003, Vista as well as Windows 2000 (Reboot required after setting the following registry key), we need to set the following registry key

1. Start Registry Editor.
2. Add the following registry value:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\ParametersRegistry Value: LogLevel
Value Type: REG_DWORD
Value Data: 0×1

If the Parameters subkey does not exist, create it.

3. Quit Registry Editor. The setting will become effective immediately on Windows Vista, on Windows Server 2003, and on Windows XP. For Windows 2000, you must restart the computer.

 

 

** IMPORTANT : This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Also please contact the Product Support Services for more help regarding the specific issue you have.