ADS user and sql 2005

[rcamarda]

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).
Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.
Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?
TIA
Rob

[rcamarda]

More Info:
I checked the server log and the error is state 6. I found a blog on
MSN and it says state 6 is 'Attempt to use a Windows login name with
SQL Authentication'.
Right, exactly what I thought I wanted to do.
I thought that when I added a windows user to a sql servers security
and login, that windows user can access the sql server??

[Henrik Fyhn]

You need to give this user explicit credentials, typically make hime a
member of a role which has the right you need.

In SQL 2000 you do this under security. It's quite simple.

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

rcamarda (ro*****@hotmail.com) writes:

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).

Mixing apples and oranges, I see. To log into SQL Server as ADS\dw,
you need to be logged into Windows as ADS\dw. That's what integrated
security is all about. By already being authenticated by Windows,
there is no need for SQL Server to authenticate you again. But you
cannot log into SQL Server with another Windows login than the one
you are logged into Windows with. You can only log into SQL Server
with an explicit username/password with an SQL login.

Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.

And dw now has sysadmin rights in the server, unless you remove
BUILTIN\Administrators.

Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?

First descide whether it's a Windows login or an SQL Login you want.
Next grant this user access to the server and database. Next you grant
him CONTROL on the database. (You are on SQL 2005, right?)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rcamarda]

Erland,
Yes I am on sql 2005. I am using Cognos' ReportNet (Now Cognos8) to
connect to its Content Store, a database. I need to provide a user and
password. I thought I would set up a user on ADS and provide the
account and password.
Out of confusion/frustration/ignorance I created a local user within
SQL server and it works just fine.
(I have several SQL servers for the database, and I thought using ADS
for user logins and authentication would be better).
So, was my problem more to do with trying to connect as another windows
user with the SQL Management tool? (I did not try to configure Cognos
since I could not connect via the SQL Studio)
Thanks for your help and any other pointers
Rob