December 31, 2003 at 1:06 pm
We are in the process of migrating DB2 applications written in Visual Basic 6 to SQL Server 2000 applications, still using VB 6. We have created a windows application trusted id for each application. This id is used for all database access, instead of granting access to each individual client id. We want to use windows authentication for the application trusted id, but are unable to connect to SQL Server when the trusted id and password are specified in the connect string because SQL Server assumes SQL Server authentication when the id and password are supplied in the connect string. Using a 'trusted connection' connect string with SSPI fails because the client's id is used and has no access.
Any help would be greatly appreciated.
December 31, 2003 at 1:21 pm
Why can't you create a group and place the agents using your application(s) into the group(s) and then create the groups in SQL and grant/deny permissions based on the groups inside SQL??
This way you only add the agent to the various different groups in the windows security area and maintain control on the SQL side by basing it on Windows group...
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 31, 2003 at 4:26 pm
I agree with whoteegan.
Also, you can't use Windows authentication in this manner if there is a domain involved.
January 1, 2004 at 1:45 pm
quote:
We want to use windows authentication for the application trusted id, but are unable to connect to SQL Server when the trusted id and password are specified in the connect string because SQL Server assumes SQL Server authentication when the id and password are supplied in the connect string. Using a 'trusted connection' connect string with SSPI fails because the client's id is used and has no access.
This can be done, but it's not exactly trivial. Some of our n-tier apps have a data access level with the COM+ components are configured to run under a single identity (and the username/password can be set under component services), but if you're talking multiple clients directly to SQL Server, this is probably not the best approach.
As whoteegan and jxflagg have indicated, using Windows groups and assigning permissions to the groups within SQL Server is the way to go. It's considered a best practice...
- Windows User within Windows Group
- Windows Group granted login to SQL Server
- Windows Group granted access to SQL Server database as a "user"
- Role created in SQL Server database
- Permissions granted to the role
- "User" made member of the database role
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
January 2, 2004 at 8:26 am
Thanks to everyone for their response. I was afraid there wouldn't be a good solution. We don't want to grant access to our SQL Server data to our clients's ids because they could then get to the data via Access or some other tool. Besides, we have a couple thousand clients with large subsets needing access to different applications. There is a large amount of turnover in our client base and userids are re-used, making maintenance of groups a pain. The applications we are migrating are client/server. We are 'under the gun' to complete the migration, so existing SQL is not being converted to stored procedures in SQL Server. The good news is that these application will be re-written at some point and will conform to MS's best practices.
Thanks again.
January 2, 2004 at 9:02 am
As you were planning to include the password in your connection string anyway, why not just use an application role if there's only one database?
--Jonathan
--Jonathan
January 2, 2004 at 9:23 am
Jonathan,
Application roles would work for a couple of the applications, but most hit more than one database. Thanks anyway.
January 2, 2004 at 10:17 am
What about creating "staging" tables that you grant Windows groups to and then have jobs run that actually perform the requests..
This way you can manage the security and the job can get done. Also, this would help set you up for the future to switch to sp's because the groups would already be in place and the cutover would be much shorter smoother??
Just a thought
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 2, 2004 at 10:34 am
whoteegan,
I'm not sure what you mean by 'staging' tables. When the applications are re-written, they will be done in .Net. We do not have a problem with using a single 'trusted id' for each application in the .Net environment.
Since the applications are going to be re-written, we have decided to just use a create SQL Server authenticated id/pw for each application and use that in the time being.
Thanks again to you and everyone else who contributed.
--garjone
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply