Allow Access only from Certain Applications
-
How do I deny access to a database from all applications except my custom application? I have an application that accesses a database using integrated security. I would like to prevent authorized users from accessing any part of the database directly using other apps such as Access, Excel, etc.
Although users have access to stored procedures only and not the underlying tables, they can still execute the stored procedure using a Pass-Through query in MS Access.
The only way I can think of accomplishing this task is to generate a unique id when the user first accesses my application and have all other stored procedures require this unique id to be passed.
Any thoughts would be appreciated.
Thanks in advance,
srv
-
You can try APP_NAME(). At the beginning of each stored proc, you can check if the app connected is allowed. For a bit of robustness, the check can be either directly or through a user-defined function that reference a config table of allowed or denied application. This only applies to direct connected apps. A weakness is a knowledgeable person can guess or determine the required application name and set their own. This is a simple way to do it but requires that you update all pertinent stored procedures.
If you require a bit more then you can use sp_addapprole, sp_setapprole, and the like.
I suggest that you implement APP_NAME() now if you need any kind of application-database connection mitigation but read up on app roles.
-
Good question and one which I've considered in the past. Seems to me that you have to limit direct access to the database (keep the only authorised users / roles / passwords secret) and then have your application log in to SQL Server using this restricted info.
Of course, that also means that your app will have to include an extra user security module - username, password and access rights etc to be stored in SQL Server.
The app would then request UserID/Password every time a user fires up the app. The app would authenticate against the data stored in the app's SQL Server database and continue or terminate as appropriate.
As SQL Server would no longer be able to usefully identify different users, you'd probably also want to add audit information to the important tables (created by, created date, modified by etc) - using the name entered when logging into the app, not the SQL Server name -> more app changes.
Not a quick job
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Application role is designed for it.
-
Using an Application role might be the best option, create an Application role for your custom application next let the role be dbo and then let your users access your data through the application. I have done the same for an application and it work just fine.
Davy Rodrigue
-
What you have described used to be a big problem at our site too, where in the past users were each user had an account on the SQL server too, and it was a major headache as the savvy end users started running adhoc queries on the system bogging it down (yeah with begin tran and the whole nine yards).
I do not know how your application was designed and whether it is components bases, but if it were web component based, you can use the "Trusted Subsystem" model. This in simple terms means that you let the middle tier components use a fixed identity (windows integrated account) to connect to the SQL servers. You grant your users access to this component. They need no direct access to the SQL Server. When they use the application (and in turn the components) connections to the SQL server are made using the component identity account, and nobody needs to know about this account since it is configured only on the server hosting the components.
-
Thanks for everyone's recommendations. The APP_NAME() function will do the trick in my case.
srv
-
I'll take the contrarian view. App_name is not a bad fix, but it can be easily hacked, programmer can set it anything they want. Approles - well, they sound good, but in practice they present some challenges, one case in particular is if you're using anything that opens a 2nd connection behind the scenes.
I agree with validating the user yourself. Most of my apps connect to sql using the same account, then we validate who is connected and/or what access they have.
There is no good solution to this, just as there is no really good way to store connection strings securely.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply