Permissions Question 2

[winntshawn]

Hello all,

I am having a hard time understanding what is happening with my SQL Server. I have a database setup that we use to track quotations produced by our salesmen. I have created an ASP/ADO front end to allow access for people to view the contents of the database, as well as giving a select few the ability to add and update entries in the database.

On the SQL Server end, I have created logins that map to an individual user's network user account (we're using Active Directory) for the users who need access to this database. I have also granted these logins access to the required database.

On the web server (Windows 2000, SP4, IIS 5.0), I have the authentication method set to Integrated Windows Authentication. When a user opens the site, it captures their Windows account name, in the format of DOMAIN\user. This info is stored in the AUTH_USER server variable. I grab this username, and insert into the connection string that I use from ASP/ADO to connect to the database, like this:

dim strUserName, strConn, dbConn

strUserName = Request.Servervariables("AUTH_USER")

strConn = "Provider=SQLOLEDB;Server=server;Database=DB;UID=" & strUserName & ";Trusted_Connection=yes;"

set dbConn = server.createObject("ADODB.connection")
dbConn.Open strConn

Now, here's the problem: when I log in with a user account that has access to the database (let's call it GoodUser), I am able to do everything that it allows me, whether it's adding or updating information in the database. However, when I log in with a generic user account (let's call it BadUser), which I haven't even created a SQL login to map to, this user is also able to make changes to the database, which shouldn't happen. I have tried using SQL Profiler to see what's going on, and it shows that both users (GoodUser and BadUser) have successfully logged in and ran queries against the database.

So, here's what I'm looking for, in a nutshell: I need a way to allow me to look at who is opening the website and logging in, and being able to determine from that what kind of access that user should have in the database. I also need people who don't have access to the database to not be able to change any information.

I'm at the point where bashing my head against the desk is looking very inviting. Any help or direction would be very, VERY appreciated.

Regards,

-- Shawn

 

[TJRTech]

Do you know what AUTH_USER is reporting?

I suspect that since you have "trusted_connection=Y" in your connection that it is ignoring the UID parameter and using the account that IIS is running under; usually the IUSER_<machinename> account. And, further, that this account has been granted db admin authority, and as such is like dbo/sa within SQL Server.

Remove the trusted connection option, debug to make sure AUTH_USER is what you think it is (if your website allows anonymous access then it is probably NULL or blank).

Regards,
TR

 

[MDXer]

what authentication mode are you using on your webserver? If your using basic then you may not be interacting with users under the security accounts you think you are.

IIS defaults to basic authentication.

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[winntshawn]

Thanks for the quick response, guys!

For TR:
When I capture AUTH_USER, it shows me the info I'm expecting (I think it's what I should be expecting) - if I am logged onto Windows as the user GoodUser, then I see DOMAIN\GoodUser; if I'm logged on as BadUser, then I see DOMAIN\BadUser.

I tried removing the "Trusted_connection=Yes" from my connection string, and now I get the foloowing error:

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'DOMAIN\GoodUser'.
/quotation/v2/inc/dbConn.inc, line 11

(Note: I modified the Domain\username to protect the innocent. )
I think this means that now it can't authenticate the user correctly, because I've removed that param from the connection string.

For MDXer:
I have Annonymous Authentication and Basic Authentication both unchecked, with only Windows Integrated Authentication enabled. I have tried it with Basic Authentication, which gives the users a dialog box to fill out to login (which is OK with me), but I still have the same results when trying to access the database - as long as you are authenticated by AD, you get access to the database, whether I have a SQL login mapped to your AD account or not.

Any other ideas?

Cheers,

-- Shawn

 

[MDXer]

This may sound stupid but are you testing this from your workstation with your logon?

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[winntshawn]

Yes, to test to make sure that authorized users can perform the necessary operations, I am testing it from my workstation. I have also tested it from various user workstations, logged on with both authorized (ie. I have created SQL logins that map to thier Windows accounts and given them access to the database) and unauthorized (generic Windows users who have nothing defined in SQL) accounts, both with the same results.

Cheers,

-- Shawn

 

[TJRTech]

There are two different logins, one for IIS, and one for SQL Server.

IIS, if configured to use "Windows Integrated" will use the same userid, and password as reported by AUTH_USER. That seems to be working because you can get pages on the website to load, correct?

SQL Server login is failing through your ADO/ODBC connection. I suspect that is happening because you are passing in a userid only, but NOT specifying a password.

The reason a TRUSTED connection didn't work, I suspect, is because it then ignored the UID param of the connection string and instead used the userid that IIS is running under, and in this case, is the IUSER_<machinename> account, by default.

There is probably a way to setup IIS to do impersonation in your ASP page, and impersonate the AUTH_USER, but that might not be worth it.

A couple of related MS articles are below:

http://support.microsoft.com/default.aspx?scid=kb;en-us;307002

http://support.microsoft.com/default.aspx?scid=kb;EN-US;247931

Lastly, I would suggest you "rethink" user-level access permissions in your database, as they may be viewed as the "root" of your problem. What I tend to do is use role level security (creating roles, etc), and use custom authentication in the ASP pages, and a database connection and database userid/pwd that is specific to the role a user has (admin, user).

Just some thoughts. I have never been able to get Windows Integrated security of IIS to successfully pass its credentials, silently through to the database, intact and make it all work happy.

TR

 

[winntshawn]

Thanks for all your help guys.

Here is what I did to finally get it working:

I was able to trap the username and password from the user when using Basic Authentication - it allowed me to use the server variables AUTH_USER and AUTH_PASSWORD, save them into an ASP variable, and then build my connection string using them, like this:

strUsername = Request.ServerVariables("AUTH_USER")
strPassword = Request.ServerVariables("AUTH_PASSWORD")

strConn = "Provider=SQLOLEDB;Server=server;Database=DB;UID=" & strUsername & ";PWD=" & strPassword & ";Trusted_Connection=yes;"

set dbConn = server.createObject("ADODB.connection")
dbConn.Open strConn

This allowed me to use the user's network account and password to give them access to the database. I have also created roles, instead of individual user accounts, to give people access as necessary.

The problem came from a couple of the other databases on the system, and the permissions applied there. Specifically, there was one database which had the Everyone group with permissions, and this seemed to give anyone permission to the database.

Now, the only issue wih the whole setup is the fact that Basic Authentication passes usernames and passwords in clear text, but I'm sure that a server certificate and SSL should clear up any issues there.

Thanks again.

Cheers,

-- Shawn

 

[TJRTech]

Yup, that will work, and yup, basic stinks because:

1) The password dialog looks crappy
2) The credentials are passed in the clear

Which is why, many used a secure connection along with custom authentiation and a single known DB login to the database in lieu of user-level database security.

Is there a reason why user-level db perms are of such importance to you? Is this a new web UI for legacy client/server application?

TR