SQL transactions as another user

[thoult]

I have a nice little API for T-SQL transactions, based on the Database Connectivity Toolkit (hi Mike, when you arrive :D).

Many of my transactions involve connecting to a central SQL database outside of my control. This is done via integrated security / SSPI. As a result, a valid domain user must be logged into Windows in order to connect and execute queries etc. Usually, this domain user is a generic account, and there are many tens of these across the company, each under control of the relevant group who use it for their production machines.

There's a view that I'd like to access which can only be accessed via a specific domain user account. Access cannot be granted to any of the generic domain user accounts, as they could possibly browse sensitive data through a viewer.

During development, my options are:

1) Log into the specific domain account to run any queries, which is fine for debug purposes but not for the long term

2) Use runas to launch SQL Server Management Studio as the specific user, which is fine for debug purposes but not for the long term

3) Use runas to launch a LV executable which connects and carries out the query as the specific user...which seems a bit clunky

So, in search of another option... is there any other way that anyone knows of to connect to a SQL server as another Windows user via LabVIEW?

To reiterate, I have no control over policies or configuration of the server, so it has to be through integrated security / SSPI, and I can't run everything as the specific user account either.

[Sam_Sharp]

Does this StackOverflow post help? http://dba.stackexchange.com/questions/66014/connect-to-sql-server-with-windows-authentication-in-a-...

I was wondering if you can specify a different domain account / password and use that with the connection string but (understandably) it doesn't look like that's possible and you have to use something like runas like you alluded to with your third option.

(It's a shame you can't create an explicit MSSQL user account with the permissions you need...)

[thoult]

Thanks for the link - I'm aware of runas, but the additional credentials suggestion is new to me.

I think I'm likely to be banging my head against a brick wall trying to get an SQL account created rather than relying on domain user account access. Frustratingly, they created this view and restricted access without thinking all aspects of it through.

In the connection string or UDL, as soon as you specify Integrated Security = True (or whatever it is, can't remember off the top of my head), the username and password are ignored.

The additional Windows credential option is possible, although it may not be possible to add another credential for the same domain (not sure), but I would be willing to bet that even with administrator rights my employer's group policy would reset it at login and periodically whilst logged in.

I honestly can't see anything other than bundling the interface into an executable and using runas 😞