0

I'm trying to help a client setup an environment for an old classic ASP application and we ran into this issue when connecting to the SQL Server database.

The error we get is "Login failed for user 'WORKGROUP\LocalComputerName$'."

Which is pretty self-explanatory. I figured it might be related to the Identity used by the Application Pool in IIS, but after changing it to a specific user I kept getting the exact same error. Then I found this article: Login failed for user 'DOMAIN\MACHINENAME$'

So, what seems to be happening is that the connection is assuming the SQL Server is a remote computer and thus it is trying to authenticate using the name of the computer instead of the current user.

Now the tricky part:

  • The environment isn't in a domain so the computer cannot be added to the users in SQL Server
  • I don't have access to the actual logic that connects to the DB, I just know that it uses a specific system DSN to do it.
  • I cannot pass arguments to the connection at all. I need to make it work via a DSN.

I can modify the DSN in any way I want, as well as the IIS and the SQL Server though.

Any ideas on how to setup a DSN that doesn't require password/username to be passed in by the code and can connect to the SQL Server using a local user instead of the computer name.

Thanks.

willvv
  • 8,439
  • 16
  • 66
  • 101
  • Does this answer your question? [What is a Trusted Connection?](https://stackoverflow.com/q/1250552) – user692942 Nov 05 '20 at 09:21
  • @Lankymart, no, I'm using a trusted connection in the DSN and when I test it it works correctly. The problem is that when IIS uses it it is passing the computer name as the credentials instead of the user credentials. Computers need to be given permissions independently. – willvv Nov 05 '20 at 15:54
  • You either pass credentials or you don't, if you are expecting to use Windows Authentication to pass through the credentials, you are trying to use a Trusted Connection. – user692942 Nov 05 '20 at 16:24
  • I know that. You don't seem to have read the question. The problem is that IIS is using the PC name as the credentials instead of the user name. My question is whether there is a way to prevent that since the SQL Server is on the same PC as the IIS. – willvv Nov 06 '20 at 17:51
  • That’s because of the way your Application Pool is configured are you using `LocalService` or `NetworkService` as the `ApplicationPoolIdentity`? – user692942 Nov 06 '20 at 18:04
  • 1
    @willvv try to add application pool identity user to the SQL server and grand permission to access db. https://forums.iis.net/post/2159167.aspx – Jalpa Panchal Nov 11 '20 at 09:28
  • As mentioned in the third paragraph of the question. I changed the identity used by the Application Pool (I tried with all the potential options for Application Pool) and in all cases the connection was attempted with WORKGROUP\LocalComputerName$ instead of the user selected for the Application Pool. – willvv Nov 11 '20 at 15:38
  • @willvv did you try to connect SQL server from any other asp.net application from the visual studio? did you face same issue while connecting the SQL server from the visual studio? – Jalpa Panchal Nov 19 '20 at 03:23
  • 1
    What's the security on the website? You need Windows Authentication turned on and Anonymous turned off in IIS for it to pass the current windows user. – LarryBud Nov 25 '20 at 16:06

1 Answers1

0

No doubt you will have created an authenticated user for read/write to the database to support your DSN connection, which by the way, can be more reliable when using "SQL Server Authentication" rather than "Windows Authentication".

But make sure that you use a 32-bit DSN connection if the site is set to allow 32-bit applications.

If using the default and site is 64-bit only then by all means use a 64-bit DSN connection.

WilliamK
  • 821
  • 1
  • 13
  • 32