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.

 

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.