December 18, 2002 at 1:42 am
Setting: Win 2K Server, Sql 2K
1. I login to the Sql box as Administrator
2. I create a local group called SQLUsers
3. I create a local NT account, called TedN, and add TedN to SQLUsers.
4. Under "EM >Security >Logins" I create the NT login "<SQLServerName>\SQLUsers" and grant that group "sa" equivalence.
5. Trouble starts when I go to QA:
5a. I use "File > Connect >SQL Server authentication", type the name TedN and its password,press Enter and get the error:
"Unable to Connect to Server "SQLServerName"
Server: Msg 18456, Level; 16, State 1
[Microsoft]{ODBC Sql Server Driver][Sql Server]Login failed for user 'TedN'
This makes sense as user TedN is not a login defined directly in Sql Server (the account
is defined on the local NT box, not in Sql server itself). Of course, I can't login in using Windows Authentication for that will use the Administrator account, not TedN at all.
5b. When I login on a remote client as TedN, use QA with "Windows Authentication" to access the Sql server, I get the same error as above.
But if I create a login for TedN (either defined locally on the SqlServer or as "DomainName\TedN" on the SqlServer) QA works just as desired (i.e. it lets TedN log in)
I expected that creating a group, granting it "login" permission to Sql, would grant
its members the same rights (i.e. the ability to login to Sql). What am I doing wrong?
TIA,
Bill
December 18, 2002 at 4:38 am
You'd have to log into the machine as Ted to test it. If you enter a password, you're entering a sql login rather than an net login. Have not tried, but you could may be able to use runas rather than having to login as Ted.
Andy
December 18, 2002 at 4:45 am
I am a bit confused here
4. Under "EM >Security >Logins" I create the NT login "<SQLServerName>\SQLUsers" and grant that group "sa" equivalence.
Are you saying you created a Group in SQL or NT/ADS and assigned to SQL? You should be creating the group in NT/ADS and put TedN into it. Then grant access to the NT/ADS group with the equivalence of SA. He will now have full control over the SQL Server.
If however you are trying to restrict to a specific DB you add his account or the nt/ads group account to SQL Logins and create a role in the DB(s) he will have this access in and add him/his group to the role.
Groups are for NT/ADS. Roles are for SQL.
December 18, 2002 at 8:01 am
quote:
5b. When I login on a remote client as TedN, use QA with "Windows Authentication" to access the Sql server, I get the same error as above.
When you log on to the remote machine as TedN, does the password on the remote machine match exactly the password for TedN on the SQL Server?
If they match, then you can "pass through" and this is a common technique for bridging untrusted domains/workgroups. The login and passwords have to match exactly, though, or it'll fail every time. I'll sometimes use it when I want to make a trusted connection to a SQL Server that's remote a buddy of mine hosts.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply