June 22, 2005 at 11:13 am
Hello guys.
I have a big issue... I have a linked server to get some (distributed) querys, I have it authenticated with SA account, but realized that any user could do something funny, like:
EXEC linkedserver.master.dbo.xp_cmdshell 'delete ...'
So I tryed to change the auth model in the linked server properties and tryed "Be made using current security context", but an ugly error raises telling me "Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
The idea is to authenticate users according their windows AD logon permissions on the linked server instead of use the SA ones.
The problem explanation is >here< and a possible solution is >this<, but tested it on lab enviroment and did not worked :'( (and the corporate AD managers didn't liked the idea of using the "trust for delegation" option)
Any of you have ever dealed with this?
Any recomendation?
June 22, 2005 at 12:48 pm
All the time. I've never met an AD admin that did like the idea of delegation, so this leaves you with SQL authentication. However, you don't need to use sa (and should not). Create new user in the target database with only those permissions that you want your linked server users to have and use that account.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 23, 2005 at 9:52 am
You could also use integrated security for a domain user account (this may make the Windows Admins a bit happier). We use domain user accounts for the services and these accounts do not have interactive login rights.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 23, 2005 at 10:08 am
Both servers use the same AD account to run SQL service, but I don't understand what do you mean by "integrated security for a domain user account"; could you explain me a bit more (or simply a link to know what is this about)?
Thanks
June 23, 2005 at 10:16 am
If your AD admins won't turn on delegation, then the only option you have is to use SQL accounts. Now, you could map a Windows account to a SQL Server based Remote User through a Linked Server Login, but like I said, that Remote User should NEVER be sa.
See the BOL entry for sp_addlinkedserverlogin for more details.
In this case the service accounts don't make any difference.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply