1

We recently migrated an application database to a new version from winserv2008/sql2008 to winserv2012R2/Sql2014. A service that pulls on this DB needs to be redesigned to accommodate some minor table/view definition changes.

Running the service code through tests under my personal security context works fine. However, installing and starting the service under .\LocalSystem is problematic. Looking at the service logs in event viewer I see that the service tries to connect to the sql server but gets a ERROR[28000] Login failed for user 'Domain\LocalSystemName$'.

The connection is via ODBC, SQL Server Native Client 11.0. Connection string is like:

"dsn=dsn_name;Driver={SQL Server Native Client 11.0};Database=database_name;Integrated Security=False;uid=accountname;pwd=accountpassword;Connect Timeout=15;ApplicationIntent=ReadOnly;"

Also, through the course of troubleshooting I've discovered/confirmed several things.

  1. The sql server credentials I am using are correct, they are also being used to connect from a SQL2016 instance to the SQL14 server as a linked server.
  2. The original service install on a utility server appears to be hitting the new database fine after the ODBC connection settings were switched.

My questions are: Have you run into anything like this before? Am I missing something simple in my connection string? Are there per machine security settings in sql14 that I'm missing?

Alexander Toptygin
  • 407
  • 2
  • 8
  • How did you migrate the application? Was it by any chance by using a backup/restore from one server to the other? – SchmitzIT May 30 '17 at 13:15
  • Is the data source actually using the native client 11.0? Or is it using the old 2008 driver? – Jacob H May 30 '17 at 13:18
  • @SchmitzIT Migration was done by an engineer provided by the application vendor, so I can't comment on the process – Alexander Toptygin May 30 '17 at 13:24
  • @JacobH the DSN is set to use the SQL Server Native Client 11.0 driver – Alexander Toptygin May 30 '17 at 13:25
  • What really bugs me is that the connection rejection is basically telling me that the service is trying to authenticate using integrated security under the LocalSystem context and ignoring the sql server credentials I provide – Alexander Toptygin May 30 '17 at 13:27
  • 1
    @AlexanderToptygin - IT's actually not using integrated security (`Integrated Security=False` in your connection string). Reason I asked about the backup is because that could hint at something called an orphaned user. It basically would hint at the user existing, but using a different GUID. If you connect using integrated security on your own account, but the application uses SQL Authentication, it'd be worth looking into. Check here: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server and let us know if the user was orphaned? – SchmitzIT May 30 '17 at 13:37
  • @SchmitzIT no orphaned users detected. The reason I think it's ignoring my Integrated Security switch is that I'd expect the error to return with my uid parameter instead of my security context. – Alexander Toptygin May 30 '17 at 13:56
  • Darn. It was worth a shot. So in the connection string, does it literally state `uid=accountname`? Or is accountname == Domain\LocalSystemName$? Does the application perhaps have its own login table? – SchmitzIT May 30 '17 at 14:03
  • @SchmitzIT &JacobH - Thanks for the help! The error was between the chair and keyboard. There are actually connections to two databases in the service. Looking at the logs the initial failures were caused by the connection to the migrated server, however - that was fixed around 3pm last friday. At that point the connection failures started coming from the 2nd server but I didn't notice the change (they're nearly identical). Fixed the 2nd server connection settings and now everything is running fine. Thank you for the help! – Alexander Toptygin May 30 '17 at 14:22

0 Answers0