最近在配置linked server出现了double hop相关的错误。以下是引用一位网友的blog,做个简单的记录,方便以后查阅。
link: http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
SQL Server 2005: “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”.
SQL Server 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQL Server connection".
The errors listed above is very typical when deploy linked server with delegation. They actually are thrown by the linked server and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances and Windows environment in most common scenario and try to make configuration steps as explicit as possible.
By using delegation in distributed query, such as linked server query, the SQL instance obtains impersonated token of the user logon credential to gain access to resources of another SQL instance, the linked server. In delegation setting, the client connection and linked server object are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. Some time integrated authentication also referred as trusted connection or Windows authentication. Linked server login can also use SQL login, but it is not discussed here.
To simplify the discussion, let’s assume two SQL Server instances are installed on machine A and B respectively. Also, let’s assume A is the middle server that has a linked server object configured which points to a SQL instance on machine B. If the client is on machine C different from A, we call it double-hop setting; if the client is collocated with middle server machine A, we call it single-hop setting. In single-hop setting, it is relatively straightforward to configure linked server to work. Believe or not, double-hop setting requires more careful configurations as you will see. This is because in single-hop setting, windows NTLM authentication, which is available in most common setting if all machines are windows, is sufficient for delegation; while in double-hop setting, Kerberos authentication is mandate for flowing user’s credential through machine boundaries from the client to the linked server. It requires windows domain, correct DNS name resolution, proper account setting in both Active Directory and SQL Server. To make sure Kerberos delegation [1] is correct becomes vital to operate distributed query with delegation. The authentication scheme required by delegation in different setting is illustrated by the following table.
Authentication scheme |
C to A |
A to B |
Single hop |
NTLM or Kerberos (C is on the same box as A) |
NTLM or Kerberos |
Double hops |
Kerberos |
Kerberos |
[Create Linked Server Object on Middle Server]
Before getting into details on how to configure other components, since I am talking about delegation in the context of SQL Server distributed query, let’s first give an example on how to configure a linked server object on A and set up its login to use delegation. To do so, you need the following two steps.
(1) Use sp_addlinkedserver to create a linked server object and name it “LinkedServer” which points to the SQL instance on machine B, SQLB.
“EXEC sp_addlinkedserver @server=’LinkedServer’,
@srvproduct=''”,
@provider='SQLNCLI',
@datasrc=’SQLB’,--the data source
@provstr="Integrated Security=SSPI; "
“
To verify if the command is executed correctly, run query
“select * from sys.servers where name='LinkedServer'”;
(2) Use sp_addlinkedsrvlogin to configure login to use self-mapping as following
“exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'”
Step (2) makes middle server A try to use impersonated token of user to authenticate to server B. To verify that the linked server is setup for “self-mapping”, run query
“select uses_self_credential as delegation
from sys.linked_logins as L, sys.servers as S
where S.server_id=L.server_id
and S.name=N'LinkedServer'
The resulting table should show the delegation column is “1”.
[Test Linked Server Query in Single-hop Settting]
Before test-drive a link server query in single-hop setting, you need also make sure that the client user can make direct query to the SQL instances on both A and B. This means that the user account, either windows domain account or a machine account, must have permission to access both SQL instances.
(3) To verify the user domain account has permission to access both SQL instances, use your favorite client tool, for example,“osql –E –S SQLA” and “osql –E –S SQLB”. If you are failing for whatever reason, please refer to [5][6].
(4) To test linked server query, run query at SQLA,
“select * from LinkedServer.master.dbo.sysdatabases”.
[Configure and Test Double-hop Setting]
To deploy delegation based linked server in double-hop setting, the followings need to be configured correctly.
(3) Kerberos in Windows mandates Windows domain. Therefore the user account needs to be a domain account and middle server and linked server need to join a domain. All machines involved in the delegation, including client machine, middle server and linked server, must have good TCP/IP connectivity between each other and to the domain controller and Active Directory. To not complicate thing further, we assume that A, B and C are in same Windows domain D and the user account is a domain account in D.
(4) The user’s domain account must NOT select “Account is sensitive and cannot be delegated” in its Active Directory properties of domain D. Please refer to [1] on how to configure this on Active Directory machine.
(5) The service account under which the SQL instance is running must be “trusted for delegation”, configured in Active Directory. If the service is running as “NT AUTHORITY\SYSTEM” or “NT AUTHORITY\NETWORK SERVICE”, the computer must be “trusted for delegation”. Please refer to [1] on how to configure this on Active Directory Machine. You need to have domain admin privilege to do/verify so.
(6) The user domain account must have permission to access both SQL instances from C. To verify, use your favorite client tool, for example,“osql –E –S SQLA” and “osql –E –S SQLB”. If you are failing for whatever reason, please refer to [5][6].
(7) If the SQL connections are to use TCP/IP connectivity, configure and verify that SQL connections from C to A and A to B are using Kerberos authentication. Please refer to [2] on how to configure Kerberos for SQL. In a nutshell, both services on machine A and B need to have a SPN created in Active Directory for SQL service. If the service running account is not “NT AUTHORITY\SYSTEM”, you need to configure the SPN on the Active Directory Machine with domain admin privileges. To verify that every hop is using Kerberos and TCP connectivity, run query
“select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid”
when (a) connect to A from C (b) connect to B from A. To make Kerberos work for both hops is crucial and some time it might not be very straightforward on what goes wrong. If fail to get Kerberos to work, please (a) verify If the SPNs are configured and well-formed according to [4]; use “setspn –L acccoutname” to verify the SPN (b) verify if DNS reverse lookups of both machine A and B return well-formed FQDNs. Use “ping –a machinename” on machine A, B and C to verify DNS works as expected, i.e. returning FQDN; (c) make sure that there is no cached Kerberos ticket on machine A and C. Use “klist purge” to purge all tickets. There might be delay before Windows local security authority (LSA) requests a new ticket from Active Directory. Sometime, you need to log out and log back in again before a new Kerberos ticket can take effect. For more Kerberos troubleshooting techniques, please refer to [3].
(8) If the SQL connections are to use Named Pipe connectivity, SQL level Kerberos is not required as opposed to TCP connectivity. This is because Windows named pipe protocol can use Kerberos to authenticate logon user under the cover. You need to verify that both machine SPNs “HOST/machinename” and “HOST/ machineFQDN” is well-formed use “setspn –L machinename”.
(9) Since double-hop can have combination of Named pipe connectivity on one hop and TCP on the other, the following table is valid configuration for delegation. Run query
“select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid”
when (a) connect to A from C (b) connect to B from A.
{net transport, auth_scheme} |
C to A |
A to B |
1 |
NP, NTLM* |
NP, NTLM* |
2 |
TCP, Kerberos |
NP, NTLM* |
3 |
NP, NTLM* |
TCP, Kerberos |
4 |
TCP, Kerberos |
TCP, Kerberos |
* Note that even SQL shows NP is using NTLM at SQL level, Windows named pipe protocol can still use Kerberos under the cover ( the SMB/CIFS windows file system, upon which the named pipe is implemented, can use Kerberos) that allows Kerberos delegation.
After verifying the above 9 steps, finally we come to the point to run a double-hop linked query. If you still encounter issues after follow these steps, please send me the error message, your configuration, the output of the queries listed for every step and information required by [6].
In discussion above, we assume the A,B and C are in the same domain. If they are in different domains, apart from verifying 9 steps listed above, you also need to make the domains involved trust each other.
Note that setspn.exe, klist.exe referred in this post can be downloaded as part of Windows resource kit. Kerberos delegation is only supported on windows 2000 and onward.
[Reference]
[1] Troubleshooting Kerberos Delegation. http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx
[2] Using Kerberos with SQL Server. http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
[3] Troubleshooting Kerberos Errors. http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
[4] http://support.microsoft.com/kb/889615/en-us
[5] http://support.microsoft.com/?id=840219
[6] http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx
相关推荐
Windows Server 2008 64位操作系统内SQL Server建立Linked Server连接Oracle
如何把Excel 文件变成 Linked Server 的 SQL源码。 好处是你可以很轻松的用 SELECT 来拿 Excel 里的数据。 如:SELECT * FROM ExcelLinkedServer
SQLSERVER访问MYSQL表中文显示成乱码.pdfSQLSERVER访问MYSQL表中文显示成乱码.pdf
If you're looking for a resource to cover data integration and ETL across the gamut of Microsoft's SQL Server toolset, SQL Server 2012 Data Integration Recipes is the one book that will meet your ...
Intent locks improve performance because SQL Server examines intent locks only at the table level to determine whether a transaction can safely acquire a lock on that table. This removes the ...
在为SQL SERVER创建ORACLE的连接服务器时,没有ORACLE provider for OLE DB 的连接服务驱动。经网上搜索,把PDF安装说明和驱动程序一起收藏,请各位下载;
UID-2 (error) There are no designs to be linked UID-3 (warning) Can’t read link_library file ’%s’ UID-4 (error) Current design is not defined. UID-5 (error) Current design ’%s’ has no schematic. ...
NULL 博文链接:https://samsongbest.iteye.com/blog/1399288
LinkedServer
readxl:将excel文件(.xls和.xlsx)读入R:linked_paperclips:
1. 安装SQL Server 数据库:SQL Server 2008 R2 Express 2. 安装Oracle 客户端访问程序:Oracle 10g Client 3. 配置Oracle 客户端tnsnames.ora 文件,其中需要注意文件中的数据库别名“ORADB”,该名称在下文创建...
OLEDB等待类型是SQL SERVER 数据库中常见的几种等待类型之一。它意味着某个会话(SPID)通过SQL Server Native Client OLEDB Provider发生了调用请求并等待数据库返回所需的数据。它出现在远程系统(remote system ...
不同数据库平台的互连一般称之为数据库的异构服务,现在各大数据库之间都可以实现这样的异构互连,只是各厂商的具体实现技术不一样,如:在SQL SERVER里面叫做LINKED SERVER,通过ODBC实现与其它数据库的互联。...
In this module we will discuss Microsoft® SQL Server™ interaction with the operating system and methodology of troubleshooting server-based problems. At the end of this module, you will be able to:...
Bug with "SQLite function is not linked" in the SQLite data provider is fixed Adaptive Server Enterprise data provider Support for the AnsiNull option in the ASE provider is added Bug with getting ...
Just as the organization of a telephone directory makes it easy for a person to search, SQL Server quickly searches a table with a clustered index. Because a clustered index determines the sequence ...
远程链接服务器详细配置 --建立连接服务器 EXEC sp_addlinkedserver '远程服务器IP','...对于SQL Server实例来说,product_name是'SQL Server' , @provider = 'provider_name' --这是OLE DB访问接口的唯一可编程标识。
You do not have to provide a copy of the SPServer license with programs that are linked to the SPServer library, nor do you have to identify the SPServer license in your program or documentation as ...
bajillion-linked-lists:练习https
代码如下:‘//Name : AttachDSNLessTable ‘//Purpose : Create a linked table to SQL Server without using a DSN ‘//Parameters ‘// stLocalTableName: Name of the table that you are...