SQL Connection String using Domain Account

[Tim Sapp]

Folks,

I am working on a .Net web site that connects to SQL Server 2000 on another
box. The DBA has given me a Domain user account with rights to the database
and table. I have confirmed with Query Analyzer that the user ID and
password are good and can access the correct tables. However, I am unable
to get a connection to the database. I either get a Login Failed for (NULL)
user. or Not a Trusted Connection, or Login failed for userID. I have a
felling that I am just using the wrong connection string but each one that I
have tried still fails. The end user must login to the site and it set up
as integrated, but I am not able to use the users ID since they do not have
access to the database.


Here is what I have tried so far.

SqlConnection sqlCON = new SqlConnection(@"Password=Password;User
ID=Domain\userID;Data Source=ServerName\ServerInstance;Initial
Catalog=DatabaseName;");

And

SqlConnection sqlCON = new
SqlConnection(@"Server=ServerName\ServerInstance;Database=DabaseName;UID=Domain\UserID;PWD=Password");

I have tried it with and without the domain name. Most of the time I just
get the standard "Login Failed for Domain\UserID" so it appears to be
getting to the server and rejected. I have a feeling that it is treating it
as a standard SQL account and not a domain account.

Anyone know what I am doing wrong?


Tim

 

[Marina Levit [MVP]]

I think in order to connect as a domain account, your program has to be
running as that domain account user. And then you would use integrated
security to pass those credentials on to sql server.

Or, you can have a username/password set up in sql server - but this would
not be associated with any user accounts. It would just be using up a user
in that sql server. Then you would pass those credentials in the connection
string.

You are sort of trying to combine these 2 together, and that just doesn't
work.

 

[Tim Sapp]

Yes I am starting to see that... I have added the Impersonate tag to my
Web.Config but now have the problem that the web site will not run under
that user ID. I have added it to the Administrators group on my dev machine
but I guess I am missing something else.


Tim

 

[Willy Denoyette [MVP]]

|
| Folks,
|
| I am working on a .Net web site that connects to SQL Server 2000 on
another
| box. The DBA has given me a Domain user account with rights to the
database
| and table. I have confirmed with Query Analyzer that the user ID and
| password are good and can access the correct tables. However, I am
unable
| to get a connection to the database. I either get a Login Failed for
(NULL)
| user. or Not a Trusted Connection, or Login failed for userID. I have a
| felling that I am just using the wrong connection string but each one that
I
| have tried still fails. The end user must login to the site and it set up
| as integrated, but I am not able to use the users ID since they do not
have
| access to the database.
|
|
| Here is what I have tried so far.
|
| SqlConnection sqlCON = new SqlConnection(@"Password=Password;User
| ID=Domain\userID;Data Source=ServerName\ServerInstance;Initial
| Catalog=DatabaseName;");
|
| And
|
| SqlConnection sqlCON = new
|
SqlConnection(@"Server=ServerName\ServerInstance;Database=DabaseName;UID=Domain\UserID;PWD=Password");
|
| I have tried it with and without the domain name. Most of the time I just
| get the standard "Login Failed for Domain\UserID" so it appears to be
| getting to the server and rejected. I have a feeling that it is treating
it
| as a standard SQL account and not a domain account.
|
| Anyone know what I am doing wrong?
|
|
| Tim

You can map a domain account to a SQL login, see CREATE LOGIN in SQL books
on line.
For instance, following command creates an SQL login that maps to a domain
account
CREATE LOGIN [mydomain\domainAccount] FROM WINDOWS WITH
DEFAULT_DATABASE=MyDB;

Willy.

 

[royoel]

SqlConnection sqlCON = new SqlConnection(@"Password=Password;User
ID=Domain\userID;Data Source=ServerName\\ServerInstance; Initial
Catalog=DatabaseName;");

Above is your connection string with one change in the data source,
after server name add another "\". I am not quite sure why, but with
our web application last night this was an issue once again.

On some servers we can use simply:

Data Source=ServerName\ServerInstance

On others we will not be able to connect until we:

Data Source=ServerName\\ServerInstance

I am actually researching this right now and ran across this thread and
I hope that you figured out what the problem was already, but if not
this might help you.