Unanswered: Restrict MS-SQL database for specific application
We are having VB.Net application which uses MS-SQL 2005 database as backend. If we use windows authentication using MS-SQL, apart from our VB.Net application, excel and other application are also connected to that database. How do we restrict the database specific for that application alone and restricting other applications (like excel, other vb.net apps) not to connect with that DB.
Actually there is no control in the VB.Net application.
We are having multiple VB.NET applications using the same MS-SQL database. Our problem is, if any one of the VB.Net application is connected to MS-SQL database and connection established, then it is possible to connect to that database using other applications too without authentication. Now, we want to set validation in the database to identify different set of application during login. Is there any options available.
I have identified that "Application Roles" feature in the MS SQL will resolve this issue. But as I haven't familar using the "Application Roles", I am not sure whether it will resolve the issue.
Application roles are not very secure. They go some way to hiding the database from novice users but they probably won't keep out a determined intruder.
The way to protect your database is in the middle tier or data access code. Either with stored procedures (and deny end user access to table) or with a server-side tier of .NET code that performs all data access. In other words, don't grant the end user direct access to the database itself.
Sample logon trigger. This will probably work on SQL 2005 with the right Service Pack (sp1 or better, i think), and has been tested on SQL 2008 SP1
create trigger Application_Only on all server
if (program_name () <> 'SQL Query Analyzer')
EDIT: Note, this should be used with EXTREME caution. You might be able to lock yourself out of your server with this. Although, I bet you can drop the trigger by bringing up the server in safe mode, I would not want to try it.
The application name is just a property in the connection string. So it's an easy matter for a user to discover what it is and even easier to "impersonate" one application from any other that allows you to edit a connection string. Enough to deter casual browsing perhaps but no real security there.
Originally Posted by MCrowley
I bet you can drop the trigger by bringing up the server in safe mode, I would not want to try it.
You can do it with DAC, which won't fire the logon trigger.