I'm reaching out for help with a painful MS SQL Server / AD issue

phpAddict

phpAddict

Active Member
So I know this isn't related to KH but I have a client that has a program using a MS SQL DB server that ties in (or is supposed to tie in) with Active Directory and am looking for help anywhere available. I've exhausted every possible solution I can think of and the issue still remains. The program has an option to login using a SQL account and it works fine when using that, but the SQL server also is set up to allow authorized AD accounts so you don't need to use a separate SQL login.

So it works when using the SQL account but not when using the AD account. First thought, something must be wrong with AD, right? Nope I've checked everything in AD, replication, creating accounts on each AD server to make sure replication was really happening, checked GP replication both ways, made sure new accounts would sign in on a workstation, dis-joined and joined 2 systems to the domain with no issue, checked DNS and DHCP. The issue is only affecting the database. So then the database must not be configured to allow the AD users access, right? No, that's fine too, I deleted and re-added the necessary AD users into the SQL server and allowed access (full access even) to the SQL server and DB. Access works locally on the server, but not beyond that. So then I began checking the SQL server config and can't find any issues there either. The same configuration works on another server, but ever since their new server was restarted last week it's suddenly began ignoring AD access to these DB's. Services are all running normally. I've spent a dozen hours just taking shots in the dark and am at the point that I'm about to spend the money to have Microsoft support assist me.

Issue began after a user ran an intense SQL query that locked up the system, they rebooted the server, and since then they've had this issue. The query only reads data and doesn't write anything, and it certainly doesn't have access to modify any server settings. I think the query locking up the system was just coincidence and doesn't really have anything to do with this issue. No event logs, no errors, and nothing useful other than "Could not connect to database"

:mad::mad::mad::mad::mad:

Very discouraged. :(

Thanks for any ideas,
Josh
 
It would be worth asking whether any changes were recently made to Group Policy. Sometimes changing a GP setting can have unintended side-effects...
 
No one has made any GP changes or would even know how. There was an issue with GP replication but that has been resolved. The only GP setting that would cause this exact issue (Access this system over the network, or something like that) has been checked and is set correctly. Thanks for the assist,
 
So when you say you checked the SQL server config does that mean you checked the SQL Server Configuration Manager to make sure that the protocols were enabled?
 
Yea, see attached. Originally, for some odd reason, TCP/IP Properties had the "Enabled" set to No, which was one of about a dozen "Ah Ha!" moments, but alas no success. Thanks @Dan
 

Attachments

  • SQLConfig.png
    SQLConfig.png
    30 KB · Views: 1,263
Hm, doesn't the application itself have a users configuration that may have gotten corrupted somehow? We have an app here that has it's own users configuration that's entirely different than the SQL server or DB users and I would think that you'd have to have something to that effect as well. This sounds to be more along the lines of what the issue may be, not protocols or even users in the SQL server or DB users.
 
I just (literally just a few minutes ago) got it resolved. :D:D:D:D:D
I would kiss myself if my lips came off. :p

So the real underlying issue still remains, the real issue being something wrong with the windows local service account. I had to create a new AD account specifically for the SQL services, changed the services from logging in as the local service account and instead login as the new AD account; everything is working as it should again. I've found some odd things happening related to AD on this new server and I'm thinking AD must not have been properly migrated when this new server was setup.

I'm a little PO'ed that I didn't look in services sooner and tried that, but it's a weight off my shoulders finally.
 
You must log in or register to reply here.
Top