IIS / ODBC / user permission

Question

IIS / ODBC / user permission

Didier CLAPIER 0

Hello,

With Windows server 2009 / IIS 10 / ODBC 64 / Classic ASP i experience a problem : We have an internat website (Intranet) for our company, In global.asa i open ODBC connection : Session_Start : Set Myconn = Server.CreateObject("ADODB.Connection") Myconn .ConnectionString = "ODBC;DSN=MyDSN;DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}" Myconn .Open

This is working perfectly when i navigate on this website, i'm a member of administrators group. My problem : when a domain user try to navigate the WAS rise a warning , saying the process terminate, and the web interface ask for user credential; when the user gives user/password it does not work , the only way to make it working it to give credential of an admin user. Strange behaviour : after rebooting the server or after iisreset, or after pool recycling, users cannot navigate until an administrator navigate once... it looks like the administrator open a pipe, and that pipe make the website working for standard user. After analysys the the WAS warning says that the process terminate because of a broken pipe, after debugging the MSADO15!CCONECNECTION::OPEN cause this process termination. This comes from MSADO15.DLL I checked process, and i can't see any ACCESS_DENIED error. I checked user permission on the server (DB, DB path, WWW path, MSADO15.DLL, ...) i can't find why a user can't open the ODBC connection for the first time. Any idea ? Thank you , Didier

Lex Li (Microsoft) 6,037 Reputation points Microsoft Employee

2024-01-31T18:33:03.6933333+00:00
Do you mean Windows Server 2019? Microsoft never shipped a product called Windows Server 2009.

The connection string indicates that this web app uses a Microsoft Access file as data source. But from the very begining Microsoft didn't recommend Access to be used for web apps and urged developers to migrate to real world database products such as SQL Server. I wrote about that back in 2009, https://learn.microsoft.com/en-us/archive/blogs/lexli/database-for-web-applications
Didier CLAPIER 0 Reputation points

2024-02-01T07:00:12.6766667+00:00

Hello, Yes , i'm talking about Windows server 2019. Thank you for your advise, but we can't migrate, in addition the problem is not Access database (mdb, accdb) related, it's about user permission and ADO library.
Yurong Dai-MSFT 2,846 Reputation points Microsoft External Staff

2024-02-01T09:30:47.68+00:00

Hi @Didier CLAPIER,
How did you configure the IIS application pool identity? Make sure it has the necessary permissions to access the required files and resources.
Didier CLAPIER 0 Reputation points

2024-02-05T13:30:58.37+00:00

Hello @Yurong Dai-MSFT , I made test with identity=ApplicationPoolIdentity and with identity=mydomain\administrator On both case result is the same. In webconfig, system.webServer/security/authentication/windowsAuthentication is enabled and system.web/authentication/mode = Windows Didier
Didier CLAPIER 0 Reputation points

2024-02-05T13:49:38.9366667+00:00

Hello @Yurong Dai-MSFT I made test with AppPollIdentity and with mydomain/administrator as well without success. In my webconfig file system.web/authentication/mode = windows and system.webServer/security/authentication/windowsAuthentication is enabled Didier
MotoX80 36,291 Reputation points

2024-02-05T15:50:23.98+00:00

I checked user permission on the server (DB, DB path

What permissions do you have set on the folder where the .mdb is? I would think that granting "authenicated users" modify access should fix it.
Didier CLAPIER 0 Reputation points

2024-02-05T17:04:49.44+00:00

Hello @MotoX80 DB path is granted (full control) to Authenticated users and Domain users ; i did this for teststing Didier
MotoX80 36,291 Reputation points

2024-02-05T17:22:16.2133333+00:00

What about the IIS_IUSRS group? (Worker process identities.)
Albert Kallal 5,586 Reputation points

2024-02-05T19:15:51.85+00:00

Hum, is there much ADO code, or could the routines be replaced with using ado.net? Using create object for a un-managed ADO object is often a problem. However, do make sure the application is compiled to run as x32 bits, and not ANY CPU or x64 bits, as that may be the reason for Create Object failing. Remember, even if you are using ado.net, (or ADO - which I don't recommend since it is un-managed code), then the bit size running of the web site has to match the installed version of the Access database engine. So, I would make sure that the project build is forced to x32 bits. But, even with the bit size issue, if at all possible, I would exchange the native ADO object with the .net one. You can use either the oleDB provider, or the ODBC one, but then at least your not dealing with server restriction in creating a ActiveX object as is being done now.
Didier CLAPIER 0 Reputation points

2024-02-06T18:40:22.6266667+00:00

Hello @Albert Kallal , if the problem was always present i would suspect code issue or something like this , but the website is working perfectly after i open it from my windows account (i'm admin). The line that cause the trouble is the database.connect .
Didier CLAPIER 0 Reputation points

2024-02-06T18:42:44.3366667+00:00

Hello @Albert Kallal , if the problem was always present i would suspect code issue or something like this , but the website is working perfectly after i open it from my windows account (i'm admin). The line that cause the trouble is the database.connect .
Albert Kallal 5,586 Reputation points

2024-02-06T18:49:06.8166667+00:00

Ok, you could is possible go to the web server, create a local account, give it enough rights, and then in the app pool, specify that user. Hence like this:

And then this:

- My spider sense suggests the issue is rights to create the object. Which as I noted, is why I suggest if possible to avoid using the un-managed ADDOB object, and use the .net one. Creating external ActiveX objects is by default locked down on most web servers. (sorry about the big text - the edit and posting here is a challenge).

1 answer

Your answer

Lex Li (Microsoft) 6,037 Reputation points Microsoft Employee

2024-01-31T18:33:03.6933333+00:00

Do you mean Windows Server 2019? Microsoft never shipped a product called Windows Server 2009.

The connection string indicates that this web app uses a Microsoft Access file as data source. But from the very begining Microsoft didn't recommend Access to be used for web apps and urged developers to migrate to real world database products such as SQL Server. I wrote about that back in 2009, https://learn.microsoft.com/en-us/archive/blogs/lexli/database-for-web-applications
Didier CLAPIER 0 Reputation points

2024-02-01T07:00:12.6766667+00:00

Hello, Yes , i'm talking about Windows server 2019. Thank you for your advise, but we can't migrate, in addition the problem is not Access database (mdb, accdb) related, it's about user permission and ADO library.
Yurong Dai-MSFT 2,846 Reputation points Microsoft External Staff

2024-02-01T09:30:47.68+00:00

Hi @Didier CLAPIER,
How did you configure the IIS application pool identity? Make sure it has the necessary permissions to access the required files and resources.
Didier CLAPIER 0 Reputation points

2024-02-05T13:30:58.37+00:00

Hello @Yurong Dai-MSFT , I made test with identity=ApplicationPoolIdentity and with identity=mydomain\administrator On both case result is the same. In webconfig, system.webServer/security/authentication/windowsAuthentication is enabled and system.web/authentication/mode = Windows Didier
Didier CLAPIER 0 Reputation points

2024-02-05T13:49:38.9366667+00:00

Hello @Yurong Dai-MSFT I made test with AppPollIdentity and with mydomain/administrator as well without success. In my webconfig file system.web/authentication/mode = windows and system.webServer/security/authentication/windowsAuthentication is enabled Didier
MotoX80 36,291 Reputation points

2024-02-05T15:50:23.98+00:00

I checked user permission on the server (DB, DB path

What permissions do you have set on the folder where the .mdb is? I would think that granting "authenicated users" modify access should fix it.
Didier CLAPIER 0 Reputation points

2024-02-05T17:04:49.44+00:00

Hello @MotoX80 DB path is granted (full control) to Authenticated users and Domain users ; i did this for teststing Didier
MotoX80 36,291 Reputation points

2024-02-05T17:22:16.2133333+00:00

What about the IIS_IUSRS group? (Worker process identities.)
Albert Kallal 5,586 Reputation points

2024-02-05T19:15:51.85+00:00

Hum, is there much ADO code, or could the routines be replaced with using ado.net? Using create object for a un-managed ADO object is often a problem. However, do make sure the application is compiled to run as x32 bits, and not ANY CPU or x64 bits, as that may be the reason for Create Object failing. Remember, even if you are using ado.net, (or ADO - which I don't recommend since it is un-managed code), then the bit size running of the web site has to match the installed version of the Access database engine. So, I would make sure that the project build is forced to x32 bits. But, even with the bit size issue, if at all possible, I would exchange the native ADO object with the .net one. You can use either the oleDB provider, or the ODBC one, but then at least your not dealing with server restriction in creating a ActiveX object as is being done now.
Didier CLAPIER 0 Reputation points

2024-02-06T18:40:22.6266667+00:00

Hello @Albert Kallal , if the problem was always present i would suspect code issue or something like this , but the website is working perfectly after i open it from my windows account (i'm admin). The line that cause the trouble is the database.connect .
Didier CLAPIER 0 Reputation points

2024-02-06T18:42:44.3366667+00:00

Hello @Albert Kallal , if the problem was always present i would suspect code issue or something like this , but the website is working perfectly after i open it from my windows account (i'm admin). The line that cause the trouble is the database.connect .
Albert Kallal 5,586 Reputation points

2024-02-06T18:49:06.8166667+00:00

Ok, you could is possible go to the web server, create a local account, give it enough rights, and then in the app pool, specify that user. Hence like this:

And then this:

- My spider sense suggests the issue is rights to create the object. Which as I noted, is why I suggest if possible to avoid using the un-managed ADDOB object, and use the .net one. Creating external ActiveX objects is by default locked down on most web servers. (sorry about the big text - the edit and posting here is a challenge).

Answer 1

MotoX80 36,291

Trace the error with Process Monitor.

https://learn.microsoft.com/en-us/sysinternals/downloads/procmon

Do an iisreset to start clean. Start the procmon trace. Browse the site with a standard user. When the error occurs stop the trace. (It will capture lots of events.) Analyze the file and registry activity that w3wp.exe references. Look for "access denied". Do you see file I/O entries for the .mdb file?

What entries do you see in the IIS and HTTP logs for that one request?

C:\inetpub\logs C:\Windows\System32\LogFiles\HTTPERR

If you still can't figure it out, enable failed request tracing and capture the error.

https://www.bing.com/search?q=iis+failed+request+tracing

Didier CLAPIER 0 Reputation points

2024-02-06T17:40:14.66+00:00

Hello @MotoX80 when i did this i found "The following threads in w3wp__myapp__PID__544__Date__01_27_2024__Time_10_02_30AM__952__Ntdll!ZwTerminateProcess.dmp are making a database operation using ADO. The call to MSADO15!CCONNECTION::OPEN originated from Vbscript!IDispatchInvoke2+193" For me this means that MSADO15 cause myapp terminated. With process monitor i couldn't find any ACCESS_DENIED, it would be too sample..... In HTTPERR, is see only "HTTP/1.1 GET / - - 3 Connection_Abandoned_By_ReqQueue" if i cancel login request after w3wp stopped Didier
MotoX80 36,291 Reputation points

2024-02-06T18:33:15.4233333+00:00

I would expect you to get some kind of error. It shouldn't crash the WP.

Did you install an Access Database Engine?

https://www.microsoft.com/en-us/download/details.aspx?id=54920&irgwc=1

On my Win11 laptop, I tried to define a system DSN, but it doesn't show up. I tried to re-add it, and it said that it was already there!

Checking the registry, I found the DSN, but in an Office clicktorun key.

I have not installed an Access Engine, so that would explain my problem. I don't know if Server 2019 acts the same way.

Run regedit and check for the DSN. It should be in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

Do you see w3wp reading that key in procmon?

This is what I get for drivers.
Didier CLAPIER 0 Reputation points

2024-02-06T18:44:58.3633333+00:00

Yes Database engine is installed, don't forget the web site is working perfectly after i navigate on it one time (i'm admin) ; i also tried a DNS less connection. I'm desperate,
MotoX80 36,291 Reputation points

2024-02-06T19:03:27.05+00:00

MS Access is not high on my list of expertise. Did you implement some kind of user security within the file?

One thought... (process of elimination)... what happens if you create a VB script (.VBS file) to just read the first record in the DB via the DSN. Get that to work with your account.

Then define a local testuser account on the server. Do a runas to open a command prompt that runs as that user. Then try running cscript to read the DB. That gets IIS out of the picture temporarily. Does that work?
MotoX80 36,291 Reputation points

2024-02-06T20:00:32.4+00:00

https://support.microsoft.com/en-gb/office/what-happened-to-user-level-security-69b362cd-503f-4e8a-a735-fb923ffb9fa3
Albert Kallal 5,586 Reputation points

2024-02-06T20:06:17.89+00:00

it is EXTREAMLY unlikely that that Access user level security is being used here, and ULS is ONLY supported for mdb files, not accDB ones. As to the original poster here, I would NOT use a DSN based connection, but ALWAYS use a DSN-less connection, since then the web site does not require elevated rights to read the file(s) in the DNS location. In fact, I OFTEN find that if I don't launch Access with admin rights, then I can't use FILE nor SYSTEM DSN's, since they reside in protected folders, and they don't work for me. So, don't include nor use nor have "DSN" at all in your connection strings. Use a DSN-less format.
MotoX80 36,291 Reputation points

2024-02-06T20:35:24.95+00:00

I hear ya, but since we didn't set up his environment, we have to follow the evidence that's presented to us. He says that he tried a DSN less connection. He said that he traced the process with procmon which should show an "access denied' error if the account cannot access some file or registry. For all we know, some other analyst could have built the mdb file and set up user level security. It's just another check on the list of "have you looked at...". I'm hoping that running cscript as a plain old user will spit out some error message that is being suppressed when run under IIS. The ultimate solution (recommendation) is to migrate the DB to SQL server.
Albert Kallal 5,586 Reputation points

2024-02-07T00:30:38.0633333+00:00

Is this a accDB file, or a mdb file? That could well matter. However, as noted, I doubt much ULS security is being used, else when you logged in as admin, and it works fine, then that again suggests this is not ULS. As noted, not practical to re-write the software, but if there is only 1-3 pages that use the database, then as noted, you can continue to use Access, but replace the create object ado, with .net ado. If one is familiar with both object models, then the efforts to do this change is minor (depending on how much code).. As noted, this is simply the environment and deck of cards you been dealt with - so narratives around changing this or that code is not a whole lot of help to you. As suggest, you might want to try creating a local user on the server, and specify that for the app pool, and not a domain user. And since the site runs with you as admin, then again, this does not look to be a bit size miss-match. As noted, using create object is much the same as using a COM object, or so called ActiveX, and out of the box, that option is locked down on a server. So, try a quick test with a local non domain user on the server -- that would be my best idea to try, and it only takes a few minutes at most. So, create that local user on the server. Logout, and then logon using that user, and see if you code runs on the server. If yes, then a good chance that external uses to the server will also now work.
Albert Kallal 5,586 Reputation points

2024-02-07T02:21:29.0333333+00:00

Just a bit of follow up and googling? You want to ensure the IIS context has rights to the folder where the ADDODB .dlls exist. Hence, this: The IUSR_computer and IWAM_computer account must be granted read permissions to the \<drive letter>: \Program Files\Common Files\System\ADO directory. So, check where the ado folder is. I may well be under Program Files(86), but granting rights to the above folder should make this work.

Share via

IIS / ODBC / user permission

Ok, you could is possible go to the web server, create a local account, give it enough rights, and then in the app pool, specify that user. Hence like this:

And then this:

1 answer

Your answer