January 25, 2010 at 2:21 pm
Hi All,
Is there any way in SQL Server 2005 to restrict the login privileges of a database user (say abc123) such that, abc123 can only connect to database from application developed in .Net or Java or any other language but cannot connect to database from SQL Server Client, SQL Server Management studio or thru SQLCMD. I tried to find a lot about it but could not get anything specific.
I know app_name() can be used to get the name of the application thru which database connection is made so hoping that there would be some of way restricting which apps can connect to database for a given user.
Thanks in Advance.
January 25, 2010 at 2:27 pm
Have you thought about using an application role to facilitate this?
http://msdn.microsoft.com/en-us/library/ms190998.aspx
Regards,
Jason P. Burnett
Senior DBA
January 25, 2010 at 2:42 pm
We considered using application roles, but we read there are some problems accessing multiple databases if guest login is disabled on other databases. In our application we have exact same scenario where in database user needs to access multiple database and the guest login is disabled. Hence the idea of using application roles was not used. Is there any other way apart from using application roles.
January 25, 2010 at 2:57 pm
That is definitely correct, the guest account would have to be granted permissions in the second database for that to work. I suppose you could use a logon trigger and then maintain a table that specifies allowable applications by login but I am not sure if using logon triggers is a path that you want to go down. I am also not completely sure that eventdata() can supply the client app name. I am interested to see if anyone else has any suggestions.
Regards,
Jason P. Burnett
Senior DBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply