SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows Authentication


Windows Authentication

Author
Message
george-1113455
george-1113455
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 13
Hello,
I'm fairly new to how Windows Authentication works with SQL SERVER 2005 so please bear with me.

I developed a VB inventory control application in Visual Studio 2008 that accesses an SQL SERVER 2005 database. The SQL SERVER 2005 INSTANCE is located on a Windows 2003 Server that is a member of a Domain. Currently only one user will execute this application but I would like to setup a Group that has read/write access to the database.

I'm not sure exactly how to set this up but I think generally I have to do the following:
1) Setup a Group Login Account to the Win 2003 Server
2) Setup the initial user as a member of the Group Login Account
3) Setup the Group Login Account in SQL SERVER (not sure how to do this)
4) Give the appropriate access rights to the Group Login Account in SQL SERVER (not sure how to do this).

I've read a bunch of manuals on this subject but I'm confused as to the "roles", user rights, etc. Can anyone help?
Gift Peddie
Gift Peddie
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8498 Visits: 14456
If there is an Asp.net application running all you need is a configuration and the .NET run time will take care of roles and permissions for you. Check below for details.

http://www.sqlservercentral.com/Forums/Topic779867-364-1.aspx

Kind regards,
Gift Peddie
george-1113455
george-1113455
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 13
The application is a Windows VB app developed in Visual Studio 2008 so I don't think your response applies.

Thanks for the response though, I really appreciate the thought!
Henrico Bekker
Henrico Bekker
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4610 Visits: 4732
In your AD create a group like "Site_DB_Auth" as an example.
Ad all the members to this AD group.
Add this group, and assign a role like "public" or whatever access you need them to have.
Map this Login Group to a DB

ok...lets start...follow step by step:


1. In SSMS, under Security,Logins,click "New Login".
2. Left of the "Login Name" text field, click "Search"
3. Change the "Location" to your domain, as it defaults to the local server.
4 In the "Enter the object nme to Select" field, type your group name eg. "Site_DB_Auth"
and click "Check Names".
It should resolve with an underlining.
5. Click "OK"

6. At the Bottom of the screen, select the drop down "Default Database" and select the DB this group needs to access by default.

7. Ok, now on the left menu, 2nd option is: "Server Roles"
Tick the appropriate boxes to assign the roles.

8. Ok, now the 3rd option "User Mapping", tick the box next to the DB you want this group to have access to.
Also below the DB list, is "Role Membership" - confirm the correct "db_*" access has been selected.
9. You should be done for a start.
10. Click OK

To confirm:

In your DB list on the left in SSMS, drop down your DB List, then the DB used, drop down Security, then drop down "Users" - check to see if your new AD group is in the list.


If I understood your query correct, then this should resolve the Authentication issue.
Gift Peddie
Gift Peddie
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8498 Visits: 14456
george-1113455 (9/17/2009)
The application is a Windows VB app developed in Visual Studio 2008 so I don't think your response applies.

Thanks for the response though, I really appreciate the thought!


It means you either did not read or don't understand it that I was suggesting you use Winform application settings to create your roles and it uses the Asp.net roles framework to resolve your users.

If you don't want to do that then you need to create app.config and create configuration setting using something similar to the previous post. I am going to look for cool client stuff and post the link of how you create the app setting if you want to do it the hard way.

This is cool client stuff it is comprehensive way to define user permissions in Winform application the person left Microsoft so it has been simplified so any developer can use it without the need to contact that person.

http://blogs.msdn.com/rprabhu/articles/433979.aspx

Kind regards,
Gift Peddie
george-1113455
george-1113455
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 13
Thank you so much for the beautifully detailed explanation! It worked like a champ! Just out of curiosity, would the same or similar procedure work for a Windows Work Group situation?

Anyway, thanks for the help!
george-1113455
george-1113455
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 13
Thanks again for your response! You are correct about my not understanding what you meant. The app.config is actually created for you by Visual Studio and of course is modifiable with regards to the connection string. I'm pretty sure I understand how DB security and access works from the application. I'm just fuzzy on the SQL SERVER side of security setup for a Windows Domain group.

I'll do some further research on your ASP.net suggestion though. Again, thanks for the help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search