using Integrated Security SSPI with User ID

[kenglish_hi]

Aloha,
I'm writing a desktop/Windows application using VB.net (MSDE 2003).
I'm trying to use integrated security with a User Id and password to
connect to a SQL Server. The SQL Server administrators insist that I
use intergrated security rather than create an account within the SQL
Server. They've made one windows account and they want my application
to log into the SQL server using that windows account. However, when I
use the intergrated security option in the connection string, the
application fails to connect. The error generated makes it clear that
the application is trying to connect as the Windows user that is using
the application and NOT the user that I have specified in the connect
string.

This is my current connect string:

Integrated Security=SSPI;Persist Security Info=False;Data
Source=MY_SQLSERVER_NAME;Initial Catalog=MY_DB_NAME;User
ID=MY_DB_USER_ID;Password=MY_DB_PASSWORD

First of all, is it possible to specify a User ID and Password when
using "Integrated Security"? If not, does anyone have a way to
accomodate the scenario I describe?

Kevin English
Honolulu, HI

 

[Sahil Malik [MVP]]

Kevin,

By default, the user running the application is the rights the application
inherits. So to specify an alternate, you need to use the RunAs utility to
run your app. (most straightforward way).

For ASP.NET apps, the story is a bit different. You need to configure
application pools in IIS Mgr.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Pablo Castro [MS]]

When you specify "integrated security=true" in the connection string, you're
asking SqlClient to pick up the Windows identity from the calling thread and
use that to authenticate against the server.

The Windows identity cannot be specified in the connection string, only SQL
auth logins can go there.

If you need to use a different account, you'll have to "impersonate" that
account. In your case, you'll most likely have to (high-level steps, I don't
have a sample handy but you can google for specific samples, there are
several out there):
- call LogonUser32 and pass user-id and password of the Windows user you
want to impersonate
- obtain a WindowsIdentity managed object from the token that's returned by
LogonUser32
- Call Impersonate() on the identity, now your thread is running as the user
you requested
- call Open() on the SqlConnection object, now you have a connection opened
as the user you needed
- before doing anything else, call RevertToSelf on the impersonation-context
object returned by impersonate to return the thread to it's original
security token.

As you can see, this is a tricky thing to do, and it implies that you have
to have the credentials (user id and password) of the user you want to
impersonate, which may imply a security risk.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.