Limiting Integrated Security Connections?

[KevinFairchild]

Ideally, I'd like to move away from using SQL-based logins for our internal applications and take advantage of integrated security instead.

Defining AD groups and their permissions in SQL is simple and getting the application to work with that is not an issue.

Where I'm having difficulty, though, is in isolating the accessibility in integrated security. Because the SQL-based login was isolated from the windows user, they could only get access to the sql server via our app -- their normal windows accounts had no access.

If we switch to use only windows authentication, the user would be able connect fine from our application and have rights to various tables. The issue is that they could also connect via Enterprise Manager, Excel, or any other tool. Is there any way to limit the exposure so that we can take use of AD for our access but further limit to allow connections based upon the application? I realize that this could be impersonated, but it's still better than nothing...


--Kevin Fairchild

 

[mrdenny]

Yes, by using an application role within the application. You have the domain account login to the SQL Server with no rights to anything. Then activate the application role to grant the rights. Without the password for the application role the user wouldn't have rights to do anything.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[KevinFairchild]

Thanks for the quick response. In looking into application roles, though, I think I can see a major downside with the way our app is currently implemented.

We do a lot of cross-database calls, since some of what we do interacts with third-party systems. From what I understand, the approle would cause the cross-database call to happen under the 'guest' context, which I can't imagine working too cleanly in some of our scenarios.

But does this only affect, say, dynamic SQL that is interacting with multiple databases or does it also affect us if a single stored procedure was called and it interacted with the different database.

I think the compromise will probably end up being that we use a single domain user account for the app. Then have the application run as that user and we then just control the stuff done in the app by checking whether the user (the real one -- not the one the app is running under) is a member of the various AD groups/roles we're interested in.

Thanks. I still don't have a definite plan yet, but I do at least have a few options now.

--Kevin

 

[mrdenny]

Correct. Application roles are database specific so they will go out as the guest account to get to other databases. Which will pretty much kill your security.

If you have cross database chaining enabled on both databases in question that would probably fix it for all non-dynamic SQL commands.

For Dynamic SQL you will still have this problem.

Impersonation is definitely an option to get around this issue.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com