Remote bulk insert solution in Windows Help (909801 not verified)

How to Ensure Kerberos authentication when creating a remote connection to an SQL Server 2005 instance

Source: http://support.microsoft.com/kb/909801

 

This document describes how to use Kerberos authentication as a Microsoft Windows authentication method when creating a remote connection to a Microsoft SQL Server 2005 instance.

 

If you are using Windows integrated authentication instead of SQL authentication, SQL Server 2005 will be provided through Windows Security SupportProgramThe interface (sspi) indirectly supports Kerberos authentication. However, when SQL server can use sspi to negotiate the authentication protocol to be used, SQL Server only uses Kerberos Authentication under certain circumstances. If SQL Server cannot use Kerberos authentication, Windows uses NTLM authentication. For security reasons, we recommend that you use Kerberos Authentication instead of NTLM authentication. Administrators and Users should know how to ensure that they use Kerberos authentication for remote connection.

To use Kerberos authentication, you must ensure that all of the following conditions are met:

• Both the server and client computer must be members of the same Windows domain or trusted domain.
• The server's service principal name (SPNs) must be registered in the Active Directory Service.
• The TCP/IP protocol must be enabled for SQL Server 2005 instances.
• The client must use the TCP/IP protocol to connect to the SQL Server 2005 instance. For example, you can place the TCP/IP protocol at the beginning of the client protocol sequence. Alternatively, you can add the prefix "TCP:" To the connection string to specify that the connection uses the TCP/IP protocol.

How to register an SPNs in a domain

After connecting to an SQL Server 2005 instance, run the following statements in SQL Server Management studio:

Select auth_scheme from SYS. dm_exec_connections where session_id =@@ spid

If SQL server uses Kerberos authentication, the "Kerberos" string will appear in the auth_scheme column in The result window.

 

For more information, seeMicrosoft SQL Server 2005 books onlineIn the following topics:

• Service subject name registration
• How to enable Kerberos Authentication including SQL server virtual server on a server cluster

This article Article The information in applies:

• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL 2005 Server Enterprise
• Microsoft SQL 2005 server workgroup
• Microsoft SQL Server 2005 express Edition

Keywords:

Kbinfo kbsql2005connect kb909801

Microsoft and/or its suppliers do not declare the applicability of files published on the server and the information contained in the graphics for any purpose. All such documents and related figures are provided "in accordance with the sample" without warranty of any nature. Microsoft and/or its suppliers hereby declare that they shall not be liable for all warranties and conditions relating to such information, such warranties and conditions include all implied warranties and conditions regarding merchantability, conformity with specific purposes, ownership and non-infringement. In all circumstances, in any lawsuit arising from or relating to the use or operation of information on the server, microsoft and/or its suppliers, in view of any special, indirect, consequential damage caused by the loss of use, data or profit, or any damage caused by the loss of use, data or profits are not liable for any consequences.