January 19, 2017 at 9:31 am
Hello all!
First post, and I'm *not* a SQL admin (but I have experience as a regular WinServ admin). I'm running into a very strange issue, and I'm wondering if any of you might have seen this before.
Background:
Database and logins were working on SQL 2008 (WinServ 2008 R2 physical machine). Per security regs, we were required to upgrade to 2014 (on a WinServ 2012 R2 virtual machine).
1) I have full admin rights on both machines, and on both instances of SQL.
2) The 2008 instance had a single database; the 2014 instance is a centralized database, where my databases were simply added to the stack, as it were.
3) I migrated the database using the backup / restore method. The backup was made while everything was working on the original server.
4) We operate in a fully managed AD environment, so both servers have service accounts implemented for this specific application.
5) It is set up in a typical split architecture - a second physical machine provides services, references database server for all SQL functions. However, the client-side is designed to connect to the SQL server directly.
Ever since the upgrade, the users in one specific application cannot log in. I keep receiving the typical "state 8" error in the logs when trying to log in remotely. However:
1) The passwords *do* match. This has been verified, re-verified, and independently verified by co-workers.
2) Testing with a .udl file returns a successful login; attempting to login from the client-side application returns the password mismatch failure.
3) I work in a secure facility, and we are behind multiple layers of firewalls / HIPS. However, I have sat side-by-side with the security admin and verified my traffic *should* be getting through.
4) The username is mapped correctly to the database logins / roles. (at least, it's the exact same as it was before the migration).
5) The new login was created while logged into both Windows and SSMS with full admin / SA rights.
6) All of the other concurrently-running databases have not experienced any login issues. (indeed, the log shows successful logins for their respective SQL user accounts).
I have gone through SQL security, registries, group policy, and anything else I can think of on both server- and client-side...nothing has changed from before, when it was working. This *is* a live "production" server...however, it's not a critical service that we can't live without for a while. We had no choice but to do the migration since we were forced to shut down the 2008 instance by a certain date...however, we tested the migration beforehand, and that's when this problem first occurred. We have been chasing it ever since with no luck.
It's almost as if the client-side app isn't sending the password to the SQL server correctly, for whatever reason. Windows system logs on both machines aren't helping, as there seems to be no trace of the event outside of the SQL logs.
Thanks in advance for any help.
Shane
January 19, 2017 at 2:48 pm
Not sure if you are talking about Windows logins or SQL logins but it sounds like SQL logins.
The passwords are case sensitive - that is one thing that hits people. It works a bit differently if it's a contained database user but that likely doesn't apply since it sounds like a straight copy from 2008 to 2014.
But the thing that I would probably look at is related to this statement:
It's almost as if the client-side app isn't sending the password to the SQL server correctly, for whatever reason. Windows system logs on both machines aren't helping, as there seems to be no trace of the event outside of the SQL logs.
So if it's just one app and the login is not "direct" but gets passed from the app to SQL Serve then looking at the app make sense. If it's a SQL login (or logins) and you can use that login and password with any other SQL Server program (SSMS, sqlcmd) then it's highly likely the application. SQL server doesn't see the password as matching the login for some reason.
Sue
January 19, 2017 at 3:02 pm
The original poster said something about a "new login". If there's any logic in the app that uses any kind of SID identifier for a given login, and there are new ones, there might be a table somewhere that needs updating with the new SIDS involved. I know they referenced SQL logins, but maybe under the covers, something else is going on, and maybe an AD user's SID is involved, and perhaps those SIDS got changed because of a change in OU or some such thing? Just trying to come up with an alternative explanation...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 20, 2017 at 7:17 am
As Steve posted, check the SID on the new login versus the user SID in the database.
SQL Server, like Windows & AD, uses a unique identifier (SID) to match up security (actually, AD has two, a SID and a GUID, but that's a different discussion). So it's entirely possible to have two accounts both named BrianK over time with different SIDs in AD if one was deleted and the second was created. That's effectively what might have happened here since you went to a new SQL Server. In SQL Server there's a way to preserve the SID to migrate the login from one server to another, but this has to be done at time of login creation.
If the SIDs don't match, check out sp_help_revlogin to get the login and user SID back in sync.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply