WIndows Authentication Connection String

[Joe Cool]

I need to be able to connect to a database on a remote SQL Server from
a .NET client using ODBC and Windows Authentication. I need to be able
to specify a Domain username and password to connect where the domain
username may or may not be the my currently logged in Domain username.
In other words, let's say I was logged into the username
DOMAIN1\user1. The username DOMAIN1\user2 has been added as a username
on the SQL Server. I would like to establish a ODBC .NET connection to
the remote server specifying the DOMAIN1\user2 username (and password
if needed, which I would suppose it is).

I have tried this with the connection string:

DataSource=servername;Initial Catalog=databasename;User
Id=DOMAIN1\user2;Password=password

And this fails to connect. All the exception message says is "Login
failed for user 'DOMAIN1\\user2'."

[Tom Moreau]

Remove the User Id and Password from the connect string. Check out:
http://www.sqlteam.com/article/sql-server-connection-string

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Joe Cool" <joeco...@live.com> wrote in message
news:be90d1ea-ed70-4d0f...@e37g2000yqn.googlegroups.com...

[Joe Cool]

On Jan 13, 3:18 pm, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> Remove the User Id and Password from the connect string.  Check out:http://www.sqlteam.com/article/sql-server-connection-string
>

You have miusunderstood my question. If I take out the username and
password and specify integrated security instead, the connection will
attempt to connect with my currently logged in domain username. I want
to be able to connect to a DIFFERENT domain username (different from
my currently logged in domain username) that had been previously
entered in the SQL Server's Security logins as a windows login.

>
> "Joe Cool" <joecool1...@live.com> wrote in message

[Erland Sommarskog]

Joe Cool (joeco...@live.com) writes:
> I need to be able to connect to a database on a remote SQL Server from
> a .NET client using ODBC and Windows Authentication. I need to be able
> to specify a Domain username and password to connect where the domain
> username may or may not be the my currently logged in Domain username.
> In other words, let's say I was logged into the username
> DOMAIN1\user1. The username DOMAIN1\user2 has been added as a username
> on the SQL Server. I would like to establish a ODBC .NET connection to
> the remote server specifying the DOMAIN1\user2 username (and password
> if needed, which I would suppose it is).

You can only log into SQL Server through Windows Authentication
with the user you are currently log into Windows as. There is *no* way
you can log into SQL Server as another Windows user.

Thus, to do this, you would need to impersonate that user in Windows, before
you event start to look at SQL Server. Whether you actually can do this,
I don't know, but I know that Windows has something called Run As, so
maybe. It would be a topic for different newsgroup anyway.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[William Vaughn (MVP)]

Mr. Sommarskog is right. Unless you execute the application using Run As,
SSPI authentication picks up the credentials from the system--not the
ConnectionString.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CFFE6212...@127.0.0.1...

[Greg]

This is not entirely true. You can log in with your current
integrated security credentials and then impersonate another login
(sql server or domain login) within SQL Server.

First, go into sql server security folder and select your domain login
properties.

Within the Properties dialog, select Securables and then select Add...

Within the "Add Objects" dialog select specific objects and OK...

Within the "Select Objects" dialog click Object Types...

Within the 'Select Object Types" dialog select "Logins" and then OK...

Back in the "Select Objects" dialog click Browse....

Within the "Browse for Objects" dialog, select the other Domain user
you want to impersonate and click OK

Back in the "Select Objects" dialog click OK...

Back in the "Login Properties" dialog, select the Grant option for
Impersonate and click OK.

All that clicking around could be accomplished by this single line of
TSQL...

GRANT IMPERSONATE ON LOGIN::[<domain user 2>] TO [<domain user 1>]

Now, simply login to sql using your domain account and run the
following command:

EXECUTE AS LOGIN = '<domain user 2>'

All subsequent TSQL commands in that session will be executed under
the <domain user 2> context.

To swtich back to your own context within that session, use the revert
command.

REVERT

....easy peasy

...Now to what I was searching for...a way to execute the command
"EXECUTE AS LOGIN = '<domain user 2>'" automatically when domain user
1 logs in....any ideas???