October 7, 2008 at 11:29 am
I posted this in the Admin sub forum. Seems like it would make more sense to be here.
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 clear this up?
Can someone point me to a SQL authentication primer, this is an area i'm sadly lacking.
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?
The BOL says that the following are needed to use Windows Authentication to create a local server to remote server login mapping.
*The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2.
*The user Active Directory property, Account is sensitive and cannot be delegated, must not be selected.
*The client computer must be using TCP/IP or named pipes network connectivity.
I'm confident on the 1st and 3rd requirement, but what is a sensitive account that cannot be delegated?
Answering any of these questions would be a great help.
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
Thanks in advance.
October 8, 2008 at 9:53 am
Hi there,
If I read your post right, it looks like you are trying to set up a linked server using Builtin\Administrators as an account. (Correct me if I'm wrong)
Builtin\Administrators is not an account, it is a group and basically it means that anyone who is a member of the local Administrators group will inherit access via the Sysadmin fixed server role, or to summarize if your account has local admin privs then it gets SA privs to the instance of SQL Server too.
There are lots of different ways to set up linked servers and without a lot more information I can't tell you which one is best for your situation. One is to create a SQL Login on the server you're linked "to", grant it some priveledges, and have have users authenticate as that user. (Not a great idea, if you do this keep the permissions very restrictive) Another is to set it up to have users authenticate as themselves. (Much better)
Have you looked in Books online about linked servers
Chris.
Chris.
October 8, 2008 at 10:50 am
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
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
------------------------------
sorry to yell but,
I HAVE NO CLUE WHY THIS WOULD CAUSE MY DOMAIN\USERNAME TO MAP TO 'NT AUTHORITY\ANONYMOUS LOGON'
Thoughts?
I've looked at the BOL's and they've helped me create my linked server using SQL Auth, but I'd really like to understand how to get Windows Auth working.
Any idea what the "Account is sensitive and cannot be delegated, must not be selected" requirement means?
You mention "Builtin\Administrators is not an account, it is a group and basically it means that anyone who is a member of the local Administrators group will inherit access via the Sysadmin fixed server role, or to summarize if your account has local admin privs then it gets SA privs to the instance of SQL Server too." But would this stop me from creating a linked server with a user that is given access through this BUILTIN\Administrator Group?
Thanks!
October 9, 2008 at 2:37 pm
As I mentioned earlier.
I have tried to create a linked server which uses Windows Authentication to Authenticate a linked server. I originally wanted to see if this could be done using the BUILTIN\Administrator. Some people indicated this might not work, or that it might work but give everyone who is a BUILTIN\Administrator access to the linked server (I really wouldn't mind this as there are few of us). Once it looked like that wouldn't work, I created a user for my domain username in both the Linked server and the Local DB instance, and then granted both of them sysadmin roles.
However, now when I attempt a test connection I get an error saying the following:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
How would my DOMAIN\USERNAME login map in any way shape or form to the "NT AUTHORITY\ANONYMOUS LOGON"????
Completely baffled.
October 10, 2008 at 10:00 am
I'm going to need more details. Just to confirm, are you using SQL Server 2005?
What do you have listed in your security tab? Did you set anything up?
What server type did you set up? Did you choose SQL Server or did you go with Other data source and fill in a connection string?
Did you change any of the server options?
Chris.
Chris.
October 10, 2008 at 10:32 am
are you using SQL Server 2005?
Yes, on both the local and the linked
What do you have listed in your security tab? Did you set anything up?
*I have a SQL Authentication User, who exists on both servers with "User" under local login, impersonate is not checked, remote user is "User", and the corresponding password on that server
*I also have a local login for the DOMAIN\USERNAME which i am trying to get working. This has the impersonate check box checked, and therefore, the remote user and remote password is blank.
What server type did you set up? Did you choose SQL Server or did you go with Other data source and fill in a connection string?
I chose SQL server
Did you change any of the server options?
I don't think so, they read as follows.
Collation Compatable: False
Data Access: True
Rpc: False
Rcp Out: False
Use Remote Collation: True
Collation Name: blank
Connection Timeout: 0
Query Timeout: 0
I have not tried to "leave the impersonation checkbox unchecked, but it would seam counter productive to enter my DOMAIN\USERNAME and password in the remote user and remote password fields respectively.
Thanks a lot.
October 10, 2008 at 11:09 am
Have you tried: "Be made using the login's current context" and then have a user who has sufficient permissions on both servers (Probably you) tried to browse the linked server catalog?
I've always struggled with security on linked servers. What's the purpose of the linked server? Just to join some tables in queries? If so you might want to consider the following option.
The other setting that works really well for data that is to set it to "Be made using this security context" then setup a SQL Login with very low permissions (read only to specific tables) on the remote server and put that in but be warned - this is not the most secure option and I think you should use it as a last resort only, but it may be ok b/c it sounded like this was more of a "team server" without anything sensitive.
Chris.
Chris.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply