May 22, 2012 at 11:49 am
In our current environment we use SQL Login for application to access Databases.
I know this is bad as application developers know the password as they have to type in connection string details @.net application.
I would like to know the best appoach for providing access to application. Any help is really appreciated.
May 22, 2012 at 12:42 pm
Microsoft recommends all clients connect to SQL Server using Kerberos authentication where possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 2:11 pm
Yeah I agree.
The solution I am looking is for Application level access.
For example :
We have appliction X and we have 1000 end users to that application. When ever users use application X ,The application will access backend using an single SQL account which is defined in application.
I need to find a way such that application X should use sinle windows act to access databases. Any help is appreciated.
Whats is the best way to configure ?
May 22, 2012 at 2:16 pm
sqlbee19 (5/22/2012)
Yeah I agree.The solution I am looking is for Application level access.
For example :
We have appliction X and we have 1000 end users to that application. When ever users use application X ,The application will access backend using an single SQL account which is defined in application.
I need to find a way such that application X should use sinle windows act to access databases. Any help is appreciated.
Whats is the best way to configure ?
What kind of app is it? ASP.NET? Windows Forms? Java?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 3:10 pm
Looks like a good place for Application Roles: http://msdn.microsoft.com/en-us/library/ms190998.aspx
You create a Windows group for all the authorized users and you add the group as a Windows login in SQL Server.
You give no special privileges to the login and you assign all privileges to the application role in the database.
Then the application issues sp_setapprole with password and acquires all the privileges of the application role.
-- Gianluca Sartori
May 22, 2012 at 3:19 pm
Possibly, a good recommendation depends on a lot of info we do not have. does the app need cross-database access? does the app need access to server-level metadata? are the 1000 users concurrent? does the app run on a web server? is a domain available or is pass-through auth an option? not enough info available to say...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 3:27 pm
May 22, 2012 at 3:32 pm
Jayanth_Kurup (5/22/2012)
as long as u follow the principle of least privileges using a sql login shouldnt be too much of a problem.ideally u want :-
domain account > login> db user> application Role/Schema/object > exact permissions such as select / execute.
Don't forget Database Role 😉
+1 on least privilege
Kerberos is preferred, then NTLM second, then SQL Login as a last resort. Application Roles work with any of those three.
If using a SQL Login all I'll add is to encrypt it in any files where you have it stored on disk, such is in a web.config.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 3:40 pm
opc.three (5/22/2012)
Possibly, a good recommendation depends on a lot of info we do not have. does the app need cross-database access? does the app need access to server-level metadata? are the 1000 users concurrent? does the app run on a web server? is a domain available or is pass-through auth an option? not enough info available to say...
You're right - I took for granted a lot of info we don't have.
Worth giving a look anyway.
-- Gianluca Sartori
May 22, 2012 at 8:19 pm
Thanks for your responces.
Here are more details:
The app I am reffering to is VB 6 Desktop application. We have a middle tier server where we have all VB6 application binaries. The middle tier has a DSN created pointing to SQL server Databses using SQL account,When end users access desktop application,The application points to middletier and from Middletier the application uses DSN to access databases.
I just want to eliminate SQL account. Here in my case I cannot create DSN using integrated windows credentials as I have to give access to all users of my company.
May 22, 2012 at 8:43 pm
sqlbee19 (5/22/2012)
I just want to eliminate SQL account. Here in my case I cannot create DSN using integrated windows credentials as I have to give access to all users of my company.
You only have two choices for authenticating to SQL Server:
- Windows
- SQL Server
If I am understanding you, you do not want to allow the token for the Windows User logged into the desktop app to all the way to the daabase because you do not want to grant each individual person access to the database server directly. Is that correct? If that is the case you can still use Windows Authentication, however you would be looking for the Windows Account running the middle-tier services to be the account that authenticates to the database. Do you know enough about how the middle-tier services are configured to know if that is an option? Do they run as a service? If so, what account do they run under? A local account on the middle-tier server or a domain account on the same domain as the SQL Server (assuming there is a domain)?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 9:28 pm
If I am understanding you, you do not want to allow the token for the Windows User logged into the desktop app to all the way to the daabase because you do not want to grant each individual person access to the database server directly. Is that correct
Yep. You got me right.
If that is the case you can still use Windows Authentication, however you would be looking for the Windows Account running the middle-tier services to be the account that authenticates to the database. Do you know enough about how the middle-tier services are configured to know if that is an option? Do they run as a service? If so, what account do they run under? A local account on the middle-tier server or a domain account on the same domain as the SQL Server (assuming there is a domain)?
No,None of our application bits run as service but the the middle tier configured with required com+ objects,dlls's but none as a service. All these com+ objects are configured to run under local admin account in component services.
I will talk to our applciation folks will get back to you with clear information. I really appreciate your time on this.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply