SQL Server limit access to local server only

Question

Is there a way to limit access from SQL Server Management Studio to SQL Server to the database server? Or to a given IP address?

The case is that a group of users (in a Windows group) are allowed access to the database via an application, but should be denied access using SQL Server Management Studio.

I read several articles about Application Roles and so on, but I need to go into the database using authenticated users.

Answer 1

management studio is also just a client so there's not much you can do to disable them there.

what you could do is to deny their win accounts access so they wouldn't be able to connect through ssms.

then in your app each user has it's own sql login they connect with, but the password must be hidden from them.

Answer 2

In MS SQL 2008 (not sure about 2005) you can write a DLL trigger on logon event

CREATE TRIGGER trigger_name 
     ON ALL SERVER 
    [ WITH <logon_trigger_option> [ ,...n ] ]
    { FOR | AFTER } LOGON

to define an application name, you can use a statement like this

select program_name from master.dbo.sysprocesses with (nolock)
where spid = @@spid

In my case this query returns 'Microsoft SQL Server Management Studio' So you can check program_name and raise an error. I don't know what will happen if you raise an error inside LOGON trigger but you can try this out.

If this will not work in the way you expected, i.e. user will not be denied access to the database, at least you can write a record to a log table about that a user is trying to access the DB using MS SQL MS so you may take some administrative measures.

However this method should be considered only as temporary solution. You should use Users, Roles and Schemas to protect your database sensitive data. Some data (passwords, credit card numbers) should be stored in encrypted columns.

Take into account that your users may connect to the database from Access and Excel, from any application that supports ODBC or native .NET client... They may even write a small VBScript to connect there through ADO. So, there are a lot of different tools and applications that may show users the data in the tables. And it is not a good way to restrict access by application name - it should be restricted using standard security options.