Controlling application connectivity using Windows Integrated Security
-
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.
-
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/DBARantMinion Maintenance is FREE:
-
Thank you for the idea. We will give that a try! Have a good weekend.
-
Could this trigger be scoped at a database level rather than at the server level?
-
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/DBARantMinion Maintenance is FREE:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply