Windows Authentication Questions

[BG12424]

With Windows Authentication, how can users be given/denied access to certain tables, given permissions on db objects, etc.?

I see that in syslogins table (SQL Server 2000), there is an isntuser field that is set with domain\username information. Will this table have all windows usernames in this table as users log into Windows?

If logging into an extranet, could the user log in using their windows login/password, then be authenticated with sql server using windows authentication?

Where can I find more thorough documentation on Windows Authentication to SQL Server and some sample database scripts how to work with manipulating user information. Links would be great.

Thanks regards,
Brian

 

[miq]

hi,
Their are lot of posts around about authentication just giva seach feature a try.

logging onto winnt from away location is possible and that authentication can be used to logon to sqlserver. You have to use remote access feature of winnt/2000.

bye
miq

 

[BG12424]

I read many of the posts already, which derived me to my question here. Thanks regards,
Brian

 

[dogramone]

Hi brian,
the syslogins table will only hold valid SQL users. If you are finding users in there that you know nothing about their permissions are being set by the windows group they are attached to. If all of your users are a part of the administration windows group they will be given DBA access to SQL if standard installation has implemented.

It's reasonably simple to control access, and here we have a policy of only using windows authentication for physical users, the only SQL only users are for applications such as web sites. The great thing about using windows security is that you can create function groups in windows to assign the users to, then assign the various function groups the level of access required to SQL.

You create roles in SQL with the required permisions, the assign the windows user(s) or the windows group(s) to the roles. BOL covers it all very well under Administrating SQL Server, Managing security. Are you the only administyrator of the machines or do you have a windows administration team as well. If you do they should be able to help also as the SQL security uses similar methodology as the windows security.


Hope this helps
Mark

 

[miq]

hi,
one thing i miss was security. You can create winnt/2000 users with active directory users and computers then in enterprise manager ->security -> logins. Create new login in the name field click ellipses (browse) and select newly created winnt user account click ok. That all to it, you have configured winnt user account to authenticate to sqlserver. obviously, you can set propery security permissions for the newly created login.

you can add that user to a server role / database role or set custom access permissions.

bye
miq

 

[BG12424]

Thanks for the posts. To answer questions above, I am not the Administrator, but SQL Server developer (not DBA, but assuming DBA responsibilities at this time, pretty scary)

Mark, you mention the only "SQL only" authentication is for web applications. That is what we are looking at doing, so would it be better to log into the application with a "SQL" user/password, or should we use the Windows user/password? Are there issues with either?

Sorry, but what is BOL? << books online? >> regards,
Brian

 

[miq]

hi,
It doesn't matter which authentication method you use. Your application can logon to sqlserver only if you provide proper userid and password information of sql server account.
Don't specify windows account info in connection string.


BOL? << books online? >> YES

Are you using IIS?

bye
miq

 

[BG12424]

Yes, we are using IIS. Thanks regards,
Brian

 

[dogramone]

Brian,
I prefer SQL ony authentication for web applications as I do not like the thought of a web site user having windows access to the domain. At least this way there is no domain user available on the web site and it makes hacking a little bit more difficult.

The SQL only user for the web application would be the user id used in the web applications connection string to run it's jobs against the database, keeping this user as SQL only. This is not the user id that the physical users use to log in to the web applicaiton, just the user that queries or updates the database. Here we maintain an audit table for all web related activity and the web application passes into the audit trail the local user that has done the work. i.e. I log into the web app as markw. I update some sales information. The web app uses it's own SQL user to update the database, say websqluser, but passes to the audit trail my login name, markw, so that I can audit who made what changes.

Is this helpfull at all Brian?

Mark

 

[BG12424]

Mark,
That would work IF there is a way to somehow replicate windows user login/password information to this sql user table ( I think this is what you are using ). They want to use the same username/password as they use for windows, so if sql only authentication is better and CAN do this, then that would be great, otherwise, I think they prefer windows authentication. regards,
Brian

 

[SQLSister]

AS dogramone said:
I do not like the thought of a web site user having windows access to the domain

In our shop our network admin adsolutely forbids us to use the windows logins on the web server or any other server outside the firewall. In this case SQL authentication is your only choice. Remember in this scheme, if they change their windows passwords, the SQL login password will not change. They will have to recognize that these are two different logins and must be changed separately. Our webusers are not our employees so this is a non problem for us, but if you have someone who can do netwrok type programming, maybe they can create a way to change the SQL user table when the password is changed? Don't know how to start this as I never mess with the internal workings of windows, but you might have someone with the experience to do this. Otherwise you'll have complaining users, I guarantee it.

 

[dogramone]

Brian,
Wouldn't it be possible for the web application to capture the user details to pass in and SQL statement?

Eg
On SQL the only windows user with access is our DBA, lets call him Bob. Bob has created an SQL user called WEBUSER that has all the required permissions to the SQL database and tables. I now log into the network as MARK. I access the web application (and windows security says that I'm allowed to access the web app) and add a record to the application (remember I'm a user and do not know that there is a database behind the app). The web application has captured my windows user information and is holding it for the session. When the app fires the SQL statement (I like to use stored procs only, no SQL statments in the web app) it sends my user details to the statement which can then be used to update tables as appropriate. In the connection string for the web app the user is WEBUSER, not MARK, but MARK is sent in the SQL statement.