Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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

share|improve this answer
    
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 '13 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 '13 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 '13 at 18:43
add comment

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

share|improve this answer
    
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 '13 at 1:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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