SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 07:51 PM
| ||||
| ||||
 ADS user and sql 2005 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 Security> Logins. 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      |
|
02-29-2008, 07:51 PM
| |||
| |||
| Re: ADS user and sql 2005 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?? |
|
02-29-2008, 07:51 PM
| |||
| |||
| Re: ADS user and sql 2005 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 *** |
|
02-29-2008, 07:52 PM
| |||
| |||
| Re: ADS user and sql 2005 rcamarda (robc390@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 Security> Logins. > 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, esquel@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 |
|
02-29-2008, 07:52 PM
| ||||
| ||||
| Re: ADS user and sql 2005 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 |
Linear Mode
