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.
Best Answer
Have you granted the SQL Server accounts the right to impersonate other users? It's a Windows setting on the domain account.