October 6, 2008 at 3:29 pm
I've got mixed mode authentication going on SQL Server 2k5. Ultimately I'm trying to get a linked server access going between 2 servers using Windows Authentication.
I've got no problems when doing this with SQL authentication, but i'm not sure how the chips fall when using Widnows Authentication.
Firstly...if I've created a User DB, and created no users beyond the defaults that are made automatically, which user does the Builtin\Administrator link to? My gut says DBO, but properties of this user shows that it's linked to SA.
Can someone point me to a SQL authentication primer, this is an area i'm sadly lacking.
Thanks in advance.
October 6, 2008 at 3:44 pm
The MS documentation for creating linked server and the associated security is a good place to start. http://msdn.microsoft.com/en-us/library/ms175537(SQL.90).aspx
Getting the security right with the linked servers can be a bit more touchy than what you might think but if you read through the docs you should do ok. Post with specific errors that you are seeing if you get into it and find that you can't get the connection.
The builtin\administrators account is a default account which maps to the sysadmin server role. I wouldn't attempt to use that for your connection. If you are desiring to use Windows auth you will need to ensure that the user running the query over the linked server has permissions at the database in question on the remote server. Again though, read the documentation.
Hope this is somewhat helpful.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 7, 2008 at 9:11 am
Aside from being bad practice is there any other reason that I shouldn't use the BUILTIN\Administrator Account? I really want to use windows Authentication, and since i don't need to bug the Networking IT guys I'd like to make it under the BUILTIN\Admin for the time being.
Here is the error I'm getting, The reason I don't understand this is that I've given (for the time being) all the privileges I can on the server. And I have no problems logging into either server with my BUILTIN\Administrator privileges.
Also, when I create the local server login to remote server login mapping. I check Impersonate.
I'm assuming that indicates to use the local login's credentials to be used on the remote server. Am I correct in assuming this?
Thanks in advance.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for LinkedServerLogin 'BUILTIN\Administrators'. (SqlManagerUI)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
'BUILTIN\Administrators' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=15007&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
October 7, 2008 at 1:13 pm
Being that SQL Server does use Kerberos Authentication I would doubt that it would allow you to use the BUILTIN\Administrator account. While it exists on both servers, it is actually a server based "account" and therefore the SID associated with that account wouldn't be something tha could be passed through Windows authentication methods. I may be wrong on this so, anyone else that wants to disagree would be great.... 🙂
Can you add your account to both instances to see if that will "pass through" ok?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 7, 2008 at 1:29 pm
I've created a DOMAIN\USERNAME login for the db instance, and a user for the two databases i'm trying to link.
Now when I try to test the connection, i get the following
sorry to yell but, I HAVE NO CLUE WHY THIS WOULD CAUSE MY DOMAIN\USERNAME TO MAP TO 'NT AUTHORITY\ANONYMOUS LOGON'
Thoughts?
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply