Connect as other user using integrated security

Question

Connect as other user using integrated security

dcain-863955

Old Hand

Points: 317
More actions
October 28, 2009 at 7:37 am

#212988

Hi,
I have an application which connects to a SQL Server database using ADO/oledb with a connection string set to either Integrated Security or username/password. A client wants all users to connect with a single windows user account rather than granting each user access, and does not want to use the SQL Server username/password (don't ask).
Anyone know whether this is possible?
I thought using keymgr would work (after seeing post http://www.sqlservercentral.com/Forums/Topic754464-359-1.aspx), but it continues to connect as me rather then an alternative that I specify.
Thanks,
David

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I don't know of anyway to do this with a desktop application. I think you could with a web application using IIS as you could set that to have it's own app pool running as a specific windows user that connects.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

happycat59 One Orange Chip Points: 29451 More actions · Answer 2

Not sure what the reasoning but if it is so that you don't need to add everyone AD names to the server you could either

- add everyone to a single domain group and grant access to that group

- use the group "Authenticated Users" and grant access to that.

dcain-863955 Old Hand Points: 317 More actions · Answer 3

Jack, happycat,

Thanks for your help. I'm not sure that what I was after is actually possible, but I have found an alternative route, which involves setting up a logon trigger to rollback access from specific users when not connecting via our app. Not entirely secure, but sufficient for the client needs:

CREATE TRIGGER [TR_ODM3_FILTER]

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF UPPER(ORIGINAL_LOGIN()) IN ('DOMAIN_NAME\USER1','DOMAIN_NAME\USER2',...) AND UPPER(APP_NAME()) != '<application name passed in connection string>'

ROLLBACK;

END;