SQL Logins and Applications ! what is the best way to manage/setup

Question

SQL Logins and Applications ! what is the best way to manage/setup

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
October 16, 2009 at 4:06 am

#139545

Hi
I was wondering what the thoughts of the community is on the use of SQL logins for applications, my questions are below.
In what situations will application roles suffice, I have read about how it works, but in applications that I have seen, e.g. an exe which expects a servername as a parameter, a username and a password. How will an application role work here.
In what applications/scenarios have other SQL server experts used application roles.
If SQL server login is to be used, what is the best practice approach of how this should be managed, or in effect how do you currently manage this at your current shop ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I have never used application role, so I wont comment on that.

But for SQL Login, I would rather keep away from it and use Windows Authentication. What we do is have a database role, add the windows login to that role. Give execute permission to the stored proc that will be accessed by the application to the database role. Just the role, not the user. We do not give DML rights directly to the table.All access is through Stored Procedures.

-Roy

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 2

But for some applications, use of windows authentication just isnt possible. I think the answer you gave me is talking about normal users, I am referring to more like Java/.NEt applications that need to connect to SQL.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Roy Ernest

SSC-Dedicated

Points: 38780

October 16, 2009 at 8:11 am

#1066910

.NET can use Windows Authentication.

-Roy

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 4

Roy Ernest (10/16/2009)
.NET can use Windows Authentication.

Thanks for informing me about that, how about non .NET applications.

Also, If for example I grant permissions to a single SQL login that an application uses, e.g GRANT VIEW SERVER STATE, BULKADMIN or even dbo.

This will mean that I will have to grant that same permission to 300+ users who use the application. At the moment, I will grant the application that level of permission, but once I start using windows authentication for users.It means that I will then be granting the user the permission, something that I wouldnt have done on a normal day, so its actually making the database less secure.

Any solutions

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Windows Authentication does not mean that the users credentials have to be taken. You will create one Windows Login, the app connects with it to do all the database work. The login you create is for the app. You set up the windows login in the web.config of your .NET application.

You can use Windows Authentication for the Tomcat server when using Java. Check the below link to see how that works.

http://msdn.microsoft.com/en-us/library/ms378428.aspx

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

What ever rights you gave to the SQL Login, you can provide for the Windows login that you created in the DB.

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;

GO

-Roy

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 7

It all makes sense now, didnt realise thats what you meant. How about applications that run in the form of exe's, application.exe <dbserver> <username> <password>

Thanks very much.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

That would be toughie.... Does the same APP connect to different DBs to do different things? Couldnt the app be modified to read the connection from a config file?

-Roy