sp_executesql N'SELECT * INTO Error

Question

Post reply

sp_executesql N'SELECT * INTO Error

herb 25632

Old Hand

Points: 333
More actions
November 29, 2012 at 5:40 pm

#270864

I see a similar thread which never got an answer. Perhaps this time…
Same query works fine on SQL_Production, but generates Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' on the 2008R2 server.
EXEC SQL_Production.HCS.sys.sp_executesql N'SELECT * INTO HCS.dbo.Sales FROM MyComputer.HCS.dbo.Sales'
SQL_Production is running SQL server 2005
EXEC SQL_Dev.HCS.sys.sp_executesql N'SELECT * INTO HCS.dbo.Sales FROM MyComputer.HCS.dbo.Sales'
SQL_Dev is running 2008 R2
All permissions are the same on both servers.
Any thoughts??
Forgot to add... If I login to the 2009 server and run the select there,it will succeed. then I can go back to MyComputer and the sp_executesql will work.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I want to start by saying that I am not providing an answer here but rather providing a little food for thought....

All permissions are the same on both servers.

But the version of SQL Server that you are having this issue with is newer by a couple versions (2008 R2). Microsoft tightens security with the realease of each release of SQL Server. In SQL Server 2012, for example, there are things you simply CAN NOT do on a SQL server if you are not a local admin on that machine regardless of if you are a dbo or logged in with SA rights. I digress.

Note: the security changes in 2008R2 for example...

If I login to the [2008] server and run the select there, it will succeed. then I can go back to MyComputer and the sp_executesql will work.

That's because, locally, it's probably using your windows credentials whereas, from your PC, it's passing the credentials for 'NT AUTHORITY\ANONYMOUS LOGON'. The fact that 'NT AUTHORITY\ANONYMOUS LOGON' can't login to your production box is a good thing.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

herb 25632 Old Hand Points: 333 More actions · Answer 2

I thought that might be an issue so I made the SQL server service login for myComputer to be my own personal login and account. The service is not running the Anonymous user anymore. But it does seem to be a permissions issue.

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 3

herb 25632 (11/29/2012)
I see a similar thread which never got an answer. Perhaps this time…
Same query works fine on SQL_Production, but generates Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' on the 2008R2 server.
EXEC SQL_Production.HCS.sys.sp_executesql N'SELECT * INTO HCS.dbo.Sales FROM MyComputer.HCS.dbo.Sales'
SQL_Production is running SQL server 2005
EXEC SQL_Dev.HCS.sys.sp_executesql N'SELECT * INTO HCS.dbo.Sales FROM MyComputer.HCS.dbo.Sales'
SQL_Dev is running 2008 R2
All permissions are the same on both servers.
Any thoughts??
Forgot to add... If I login to the 2009 server and run the select there,it will succeed. then I can go back to MyComputer and the sp_executesql will work.
Thanks

this is a classic example of the "Double Hop" problem. You use windows credentials to connect to server 1, then execute a call using a linked server to server 2. Server 1 isn't passing your window credentials to server 2.

This is easy to fix if you know how kerberos auth and kerberos delegation work.

Here's about as brief as I can make an explanation of how kerberos delegation works:

for user myDom\joe to connect using kerberos to a sql instance on server1 running as myDom\sqlSvc:

1. user myDom\joe requests a token from the domain controller (DC). The request is basically "I'm joe, and I'm going to connect to sql server on server1". The DC signs it with myDom\joe's private key, encrypts the token with myDom\sqlSvc's public key, and returns it to joe.

2. joe sends the token to server1.

3. sqlSvc decrypts the token, then uses joes public key to verify the signature. Success!

How does the DC know to use myDom\sqlSvc's public key to encrypt the ticket? The answer is a SPN (server principal name). A SPN is an active directory object that is attached to a user account, and contains a machine and service name. For example, "MS SQL Server on server1" would be attached to the myDom\sqlSvc account.

Things that can go wrong:

a. No SPN = DC doesn't know who to encrypt the ticket for, Kerberos fails (silently).

b. Misplaced SPN ("MS SQL Server on server1" attached to the wrong service account) = DC generates the ticket just fine, but the target service can't decrypt it. Kerberos fails loudly (this will show up in windows error logs)

c. Duplicate SPN = DC has more than one option for who to encrypt the ticket for, therefore, it picks no one, fails silently (just like #1)

check these with the command line utility "setspn.exe"

Ok, Now assume that myDom\SqlSvc wants to connect to Sql running as myDom\sqlSvc2 on server2:

4. sqlSvc sends a request to the DC "I want to connect to sql running on server2 on behalf of joe. here's the ticket joe sent me. Please, please, please send me a new ticket."

5. The DC checks to see if myDom\sqlSvc is flagged in AD as "Trusted for Delegation". If it is trusted, it generates a ticket that sqlSvc can use to impersonate joe when it connects to sqlSvc2.

6. sqlSvc sends the ticket to sqlSvc2 and Success!

what goes wrong?

d. missing/misplaced/duplicate spn for "sql on server2"

e. joe's account is flagged in AD as "this account is sensitive, so do not delegate"

f. sqlSvc's account is not trusted for delegation.

g. sqlSvc's account is "trusted for delegation to the specific services" and "Sql Server on Server2" isn't one of them.