Sql-server – How to get the linked server working using Windows authentication

authenticationkerberoslinked-serversql serversql-server-2008

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Best Answer

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

Your Windows Account must have access to both ServerA and ServerB.
You must not have the setting "Account is sensitive and cannot be delegated."
Both ServerA and ServerB must have their own SPN registered.
The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation": http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Related Solutions

Sql-server – getting a “login failed” when creating this linked server

Run below TSQL :

EXEC master.dbo.sp_addlinkedserver 
                            @server = N'serverB', 
                            @srvproduct=N'', 
                            @provider=N'SQLNCLI', 
                            @datasrc=N'serverB', 
                            @catalog=N'MyDatabase',
                            @provstr='Integrated Security=SSPI;'
GO
EXEC sp_addlinkedsrvlogin 'serverB', true  
go

Check if the "serverB" exists in sys.servers :
```
select * from sys.servers
```

Check if the "serverB" works from ServerA

select * from serverB.master.dbo.sysdatabases

Sql-server – Windows Authentication for SQL Server Linked Servers

The only way you can pass Windows Credentials between servers to my knowledge is via delegation as you've already taken a look at. If there's truly no way to get delegation working in your environment, an alternative to delegation is to fall back to mapping your Domain accounts to SQL authenticated accounts on the remote server that are setup with permissions that are equivalent to your needs. The downside here is any query executed remotely will be logged to the SQL Authentication account and not your AD Account, so audits won't be as transparent unless you take pains to map a one-to-one SQL Authenticated account to AD Account.

To map AD accounts to remote SQL Authenticated accounts, you first need to create the SQL Authenticated accounts on the remote server and then configure local logins to map to them on your linked server. If you're happy to use the GUI, you can find this window under Server Objects → Linked Servers → Linked Server Name → Properties → Security Page:

If you prefer TSQL instead, you can perform this same functionality via the sp_addlinkedsrvlogin stored procedure.

Final Note Here: Because you don't have delegation setup, you'll want to leave the Impersonate checkbox unchecked or if you're using TSQL, the @useself parameter should be passed in as FALSE otherwise it will try to pass through authentication which won't work in your scenario.

Best Answer

Related Solutions

Sql-server – getting a “login failed” when creating this linked server

Sql-server – Windows Authentication for SQL Server Linked Servers

Related Question