Windows Authentication with non current user name

[prem]

Hello Everybody,

I have a client program for which I need to support Windows authentication.
I used the following SQL connection and it works for the current user
"server=localhost; database=myDB;Integrated Security=SSPI;"

But, I wanted to login using other windows login name ( i.e., not the
current logged on user ) and I used the following conection string
"server=localhost; database=myDB;Integrated Security=false;User
Id={0};Password={1};"
for which I got an error "Login failed for user <username>".

I need a way to authenticate SQL Server with a windows login name.

FYI: I have my SQL server configured for both Windows and SQL authentication.

Thanks in advance.
-Prem

 

[Sylvain Lafontaine]

Remove the {0} and the {1}. See the following references for more info:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

 

[prem]

Hi,

Thank you very much for the URLs.
I have gone through them and tried few. No luck.
I also did a google and found few places where the following connection
string was used
"server=localhost; database=myDB;Integrated Security=false;User
Id=myDomain\myself;Password=mypassword;"
But it does not work for me.

Thanks.
-Prem

 

[Sylvain Lafontaine]

I've just just re-read your first post and I noticed the following: « ... to
authenticate SQL Server with windows login name»: you cannot do this (use a
windows login and password as a way to authenticate on the SQL-Server).

Windows authentification is for Integrated Security and SQL Authentification
is for SQL accounts. You must create login accounts on the SQL-Server and
use them if you want your users to provide an account name and a password.

 

[Robert Morley]

You haven't mentioned what you're trying to do here, so I'm going to float
this in the hopes that it'll do you some good:

If you're trying to create a SQL connection as a specific Windows user
instead of the current user, you always have the option of using the Run As
command to impersonate someone using Windows security. I know you can do
this on Win2k or above, and I think you can do it on Windows NT as well.
Simply enable the "Run As" service (in XP, it's called "Secondary Logon"),
then you can either hold the shift key down and right-click on the app, or
create a shortcut with specific credentials.

At that point, you can log on with regular SSPI (i.e., no user name/password
required), and it'll think you're whomever you're impersonating, rather than
yourself.

That's a broad overview of the instructions, and assumes a certain level of
knowledge...if you need more specific instructions, let me know.



Rob

 

[prem]

Thanks a lot for your responses, that was really helpfull.

Windows authentification is for Integrated Security and SQL Authentification
is for SQL accounts. You must create login accounts on the SQL-Server and
use them if you want your users to provide an account name and a password.

I did try creating the windows user in the SQL by Create new user, selecting
the Authentication as Windows and entering the Domain and user name ( I see
the user name under the Users section too ). I tried the same user name in
the connection string as
"server=localhost; database=myDB;Integrated Security=false;User
Id=myDomain\myself;Password=mypassword;"
Is this supposed to work ( which is not working for me ) ?

Thanks a lot,
Prem

 

[prem]

hi,

Thanks for the response.
I will look into the direction you mentioned. I guess Impersonation is what
I should do if I cannot achieve through connection string.

Thanks.
-Prem

 

[Sylvain Lafontaine]

No, this doesn't work this way: all Windows accounts can only be used with
Integrated Security = True because only Windows can authenticate them, not
SQL-Server. This remains true even when you add them as new users on
SQL-Server.

If you want your users to provide an account name and a password or to write
them in the connection string, then you must create new SQL-Server logon
accounts under the Security tab and give them sufficient permissions to
access the relevant database(s).

 

[prem]

That was very helpul.

Thanks.
-Prem

No, this doesn't work this way: all Windows accounts can only be used with
Integrated Security = True because only Windows can authenticate them, not
SQL-Server. This remains true even when you add them as new users on
SQL-Server.

If you want your users to provide an account name and a password or to write
them in the connection string, then you must create new SQL-Server logon
accounts under the Security tab and give them sufficient permissions to
access the relevant database(s).

 

[aaron.kempf]

yeah.. this is almost ridiculous

Windows Authentication isn't as powerful as sql authentication.

what you should do is just use SQL authentication for this and there is
a special SET USER command where you can emulate another user.. right?