0

I'm trying to connect and getting an error like:

Login failed. The login cannot be used with Windows Authentication

I'm using mirrored local accounts on SQL Server and web server because I'm simply trying to use a trusted connection between machines not on the same domain. It seems like something that would be fairly common, but after days of trying to find an answer that applied to my situation, I do not recognize that any of the answers were applicable... that or the fact that I'm only a .NET developer and not a sysadmin or DBA means I have it and just don't know it.

Here's what I can say:

  • CMS web server: ASP.NET 4.0 web app running on Windows Server 2008, IIS7, on corporate DMZ
  • CMS database server: SQL Server 2008 R2 on a domain server
  • For various reasons that I won't go in to, encrypting the credentials is not sufficient.
  • The database port that the application needs to connect on is not the standard port and is set up to listen on a different port.
  • Setting up DB mirroring isn't an option and doesn't really address the requirements (in my way of thinking)

Also, FYI if this is helpful to know:

  • Content entry happens on internal web server which publishes content to same DB that is accessed by the DMZ web server.

  • Content server is set up to have the website run the app pool in integrated mode with a windows domain user that has been set as a service account using the -ga switch and given all the appropriate rights and everything runs perfect.

  • External web server, without domain access, using mirrored accounts (same username and password set up on the SQL Server machine and web machine).

  • Local account on the web server set as a service account using -ga switch and running as the app pool identity for my app.
  • On the SQL Server box, created local user with identical creds and given same permissions that the domain user identity has
  • Connecting to SQL Server via MGMT studio installed on web server with the SQL Server user creds works fine.

Now, if I put in the username in to the connection strings, everything is perfect. As soon as I put the trusted_connection=yes in the connection string like I did with the domain server connection strings, I get the trust error.

So if my connection string is like this, it works fine: 

Server=myServerAddress;Database=myDataBase;Username=MyUser; Password=MyPassword;

If I change my connection string to either of these, it fails:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

or

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

I'm working with one of the client's network admins and he's not necessarily a DBA and doesn't have experience with configuring web apps. So it's possible that we're missing something and any advice or ideas would help. What am I missing?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
divamatrix
  • 1,126
  • 1
  • 7
  • 21

1 Answers1

0

If using SQL credentials are not an option, then you should use Active Directory and create a one-way trust (have DMZ server domain trust your internal domain), create a service account in your internal domain which your DMZ application can use, and grant that account access to the database. Then it will be able to generate SSPI context.

moribvndvs
  • 42,191
  • 11
  • 135
  • 149
  • So basically, what you're saying, if I understand you, is that if creating a trust is not an option, and the possibility of encrypting the credentials is not an option, there is no way at all to avoid putting plain text credentials on the server? – divamatrix Nov 09 '12 at 02:01
  • I've actually been reading up more on this. So Windows Auth in SQL should fall back from Kerberos to NTLM, where NTLM conceptually could allow synced accounts to work (Kerberos requires a trust, hence my original answer). So, assuming the accounts are both **local** accounts, the app pool is set to use that identity, Windows Auth is enabled on the SQL server, and the local account is granted access, this might work after all. I suspect some additional work might be required to get NTLM working right, particularly with at least one of the machines in a domain. Someone on Serverfault may know. – moribvndvs Nov 09 '12 at 11:26