Sql-server – Linked SQL Servers

distributed-databaseskerberossql-server-2005sql-server-2008

I'm trying to link 2 SQL Servers (2008 and 2005). I have tried it via the GUI and scripts, and having no luck. I have problems trying to to query through it with access. As I'm not the Admin of the network, I'm working hard on investigating how to success on this but I always receive a message telling me that the user "../Anonymous Logon" is not allowed to connect.

After some research, I believe this is a problem related to Kerberos that is not transferring my credentials to the machine I'm trying to link to. I'm using NT Authentication and I have no possibility to use/create SQL Authentication logins.

I've already sent a mail to my network Admin to see if we are able to use Kerberos delegation here.

What would be the next steps to find out what is happening to my configs and why this isn't working?

Thanks in advance and, as this is my first question, hello everybody!

Best Answer

You need to talk to your network admin to see if Kerberos delegation is enabled, and whether it's constrained or not (if it's enabled and unconstrained that is a massive potential security risk, just by the by). If it is constrained then you need to register a SPN (Service Principal Name) for your SQL Server and bind your SQL Server account service to that SPN in your AD configuration. http://windowsitpro.com/security/kerberos-delegation-and-troubleshooting has an explanation of it from the point of view of a web server that wants to pass user credentials to a SQL Server (whereas what we're doing here is passing user credentials from SQL Server to a second SQL Server, but the principle holds).

If Kerberos delegation is not enabled and your network admin refuses to budge on that (and you can't create SQL logins), then you're out of luck and need to escalate via your management (or think of an alternative to using linked servers), since NTLM authentication (which is the fallback if Kerberos isn't enabled) doesn't work with linked servers.

Related Solutions

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

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

SQL Server – Cannot Register SPN, Error 0x80090350, State 4

The error message you're seeing, 0x80090350, is defined as:

c:\util\Err>err 0x80090350

# for hex 0x80090350 / decimal -2146892976 :
  SEC_E_DOWNGRADE_DETECTED                                        winerror.h
# The system detected a possible attempt to compromise
# security.  Please ensure that you can contact the server
# that authenticated you.
# 1 matches found for "0x80090350"

I'm looking this up with the Exchange Error Lookup Tool.

According to the information in this post, this error is often caused by the MaxTokenSize issue caused by an account (indirectly or directly) being a member of a large number of groups.

Another possibility I'd consider is that a duplicate SPN exists. You can determine which SPNs Windows thinks are duplicates by running setspn -X -F (info here).

Best Answer

Related Solutions

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

SQL Server – Cannot Register SPN, Error 0x80090350, State 4

Related Question