1

Is it possible to use a non privledged Windows domain account to impersonate itself in a linked server?

And why would it be unable to read the registry for available network protocols?

Overview: Only way I am able to have a scheduled job utilize a linked server is when the local account is mapped to a remote SQL account. Unable to use 'Impersonate.'

Details:

  • Two SQL 2008 R2 Std instances on Win Server 2008 R2 x64
  • One default + one named
  • I'll use Server_A_Default + Server_A_Named to refer to the instances
  • Each instance has it's own AD service account for MSSQL + Agent (4 unique AD accounts in use on server)
  • Port hard coded for Named instance Server_A_Named
  • SPNs created for the 2 MSSQL accounts.
  • SPNs match the default and hardcoded named instance port respectively

Within the named instance (Server_A_Named):

  • Created a linked server on Server_A_Named to Server_B. We'll call the linked server SAN-B.

In SAN-B, I've used SQL Nativue Client 10.0 + OLE DB Provider for SQL

Under the Security for SAN-B, I have 3 accounts:

  • NonPrivADuser
  • ADuserSysAdmin
  • LocalSQLuser

For logins not defined, connections will not be made.

As ADuserSysAdmin, I can click on test connection and it works.

Only way to get linked server to work for NonPrivADuser is to have it map to a local SQL account on Server_B NonPrivADuser has access on Server_B's database as well.

This is the error that NonPrivADuser receives while trying to access the linked server using 'impersonate':

Executed as user: DOMAIN\NonPrivADuser. SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. [SQLSTATE 42000] (Error 65535) OLE DB provider "SQLNCLI10" for linked server "SAN-B" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "SAN-B" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

I fired up procmon on Server_A while trying to use the linked server, SAN_B.

SQLAGENT.EXE can read HKLM\SOFTWARE\Microsoft\MSSQLSERVER\Client\SNI10.0 SQLSERVR.EXE receives a BAD IMPERSONATION on the same key.

I fired up regedit and 'users' has read permissions on that key.

Improve this question

2 Answers 2

Reset to default
1

Have you granted the SQL Server accounts the right to impersonate other users? It's a Windows setting on the domain account.

Improve this answer
3
  • In looking at the local sec policy, I saw SERVICE was listed under the "Impersonate a client after authentication." Had read somewhere that since the accounts were running as a service, that I shouldn't have to explicitly add them. Is that the setting you're referring to? And I can manually add them if need be.
    – Hassle2
    Mar 26, 2013 at 18:03
  • No, it's a property of the domain account that the SQL Service is running under. In AD Users and Computers find the account that SQL is running under. Edit the account. On the Delegation tab select "Trust this user for delegation to any service". Restart SQL Server.
    – mrdenny
    Mar 26, 2013 at 18:40
  • Ahh. Gotcha. I have already configured the SQL Server account for unconstrained kerberos delegation. And the instance / server had been restarted few times since.
    – Hassle2
    Mar 26, 2013 at 18:43
0

I was bitten by that horrible error message as well. Check out this kb - I believe it is your answer:

http://support.microsoft.com/kb/2492477

Improve this answer
1
  • So I cannot use a non sysadmin AD account as a job owner that impersonates itself within the linked server? Seems very strange as I was under the impression that MS wanted to guide people towards Windows authentication? The errors in the kb look very close. Except they don't mention getting the protocols list from the registry.
    – Hassle2
    Mar 27, 2013 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.