SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Controlling application connectivity using Windows Integrated Security


Controlling application connectivity using Windows Integrated Security

Author
Message
LeeFAR
LeeFAR
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1619 Visits: 338
For some time now, we have been using SQL Accounts with SQL Account Passwords in our application connection strings to have our application connect to a server and connect to a database to execute stored procedures (in a SQL Server 2000 environment). We are now looking to shift to using the users’ Active Directory accounts to allow access to a SQL Server and to Databases (SQL Server 2005 environment). We would do this by placing the “application users” into an AD group and create a login for the AD group. Then the AD group would need to have the ability to execute stored procedures. But the catch is we only want this ability to be within our applications. We do not want these same users to use Access or Excel or InfoPath to connect to the server and to the database and execute stored procedures. In know there are “Application Roles”, but there are limitations to using these (connection pooling, distributed query issues, protecting the “password” for the approle, etc). So my question is how are you doing this in a 2005 environment? I read the following in a MS paper.

“The new, preferred alternative to application roles is to use execution context in code modules. For more information, see Execution Context in this paper.”

The Execution Context section talked about using the EXECUTE AS. I also found this blog http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx. But I don’t see how this solves the problem with preventing only certain applications to execute stored procedures.

Thanks for any guidance you have.



KenpoDBA
KenpoDBA
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3980 Visits: 652
If you go under the assumption that pretty much anything you can trace on you can get access to in tsql as well, your problem could have an elegant solution.

Try logon triggers. You could test everyone's login when they logon and if the applicatoin isn't what it should be for that login, you can log them back off. So if you've got only a single login that you want to have access to those SPs, you can give only that login execute on the SP and then through a logon trigger, limit which apps can logon with that acct.

So here's a sample of what you might do: I just wrote this out real quick as an example, so it's not very rich, but you get the idea...

ALTER TRIGGER tgrLimitUsers
ON ALL SERVER WITH EXECUTE AS 'dbo'
FOR LOGON
AS
BEGIN

BEGIN
IF APP_NAME() NOT IN ('SQL Server Management Studio')

Begin
ROLLBACK;
End
END
END;


So this makes sure that only SSMS can access SQL through whatever login it is... so you'd also look for the login as well... so just put ORIGINAL_LOGIN() in your test as well and you'll have a very flexible solution. And it works for sql and windows accts.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


LeeFAR
LeeFAR
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1619 Visits: 338
Thank you for the idea. We will give that a try! Have a good weekend.



LeeFAR
LeeFAR
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1619 Visits: 338
Could this trigger be scoped at a database level rather than at the server level?



KenpoDBA
KenpoDBA
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3980 Visits: 652
I'm not entirely sure. Somewhere in the handshake the client tells sql which DB it wants to be in. You can try to test for the DB_ID() function, but I'm not sure that'll get you what you're after. I'd honestly have to play with it a little to see, but off the top of my head my guess is that it probably isn't going to get it. You can try it though. You might get lucky if the DB in question is the default DB for the acct.

If you make it so that the acct can only access that one DB though, you'll be where you need to be.

Another idea would be to setup a job every min to run through sysprocesses and check that nobody's running those SPs from a different app. This takes a little more coding, but you can use it to disable their accts and even send you an email with the relevant info. Then you can handle it on the human side as well by doing whatever it takes to make sure they don't do it again. I'd try the trigger approach first though.

You know though, now that I think about it, there's another way to do this too. You can modify the SP to capture the App_Name() as well, and if it's not the app you want, just return. You'll use the same criteria you did in the trigger, only it'll be a check you run as soon as the SP kicks off. This is actually a pretty good one I think cause you can control it pretty well that way. Of course if you have 300 SPs to do this with you'll be busy, but what can I tell you...

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search