The situation is this: simple network, one server not a domain controller, running SQL Server 2008 Express (64bit). 8 workstations running windows 7 pro connected to it. Last week after Windows updates finished up on the workstations and server, one workstation was denied log on access to the SQL Server. It can see the server and files stored on it, access those file but not log on to the SQL Server and it's DB for our CRM software.
I set this network up over a year ago, upgraded from a Windows server 2003 running the SQL Server 2005 Express on a domain (AD on a separate box) to the current stand alone Server 2008 R2 (no domain) and moved the DB over and figured out how to allow access by creating user accounts with passwords on the server. Use the same user names and that password when connecting to the server and everything was fine.
So why after windows updates does its thing would one work station be unable to log in again? (Yes I have uninstalled those updates and tried re-connecting, no luck.)
Given that I had done this before in the past, what am I missing this time around?
I read up the 'suggested questions that may have your answer' while writing this and all of the answers are things I have already done/considered with no positive result.
I have created a new user with a password in SQL Server, created matching windows account for them on the server with same password, reset the workstation's machine and user name to match, went into control panel\credentials and reset those forcing a fresh log into the server to pass those credentials and that all verifies when I log onto the server in Windows Explorer, it asks for the ServerName\UserName + password, I enter that, it accepts it. Then when trying to connect the SQL server, it fails and the message is 'make sure you have the proper credentials etc...'
I am certain I am over looking one 'little tiny devil of a detail' so I am hoping someone here can assist in uncovering it.
When using SQL Server Management Studio, I see:
ServerName\InstanceName, authentication is set to Windows authentication for when I log into it.
The server itself has mixed mode SQL Server / Windows Authentication enabled. Named pipes are setup as well as TCP/IP. (All other workstations can log in and connect)
When I create the new user name it is passed through to the instance DB and it has the same parameters as all the other accounts but it is still unable to log onto the server.
I feel as though I missing a fundamental 'how to set up user access to SQL Server using mixed mode authentication' step.
I appreciate everyone's time in reading this as well as any and all assistance given.
Ok, About ten days after this initial post I had to restart the SQL server due to an unrelated program issue. (MYOB de-syncronized over the LAN and when it does that it does not log the user out so they cannot log in unless you restart the server.) When I did this whatever windows updates responsible for the changes to the way the SQL server is accepting log on's affected the rest of the workstations on the network.
So now no one can see the database using the customer management software over the LAN.
Situation is now this:
CRM software will run locally on the SQL server fine. Able to run multiple instances of it. (we all RDP into the server and log onto the CRM software fine.)
If I uninstall the CRM software from a workstation, scrub the registry to ensure that it is not going to insert legacy data during the re-install, then restart the workstation.
Re-install the CRM software from the installer on the SQL server (means the workstation has already passed log on credentials to the server (ok, not necessarily the same as the SQL server I know but the SQL server is set to use windows authentication so..), when it gets to the stage where it asks you to point it to the SQL server from a list of SQL broadcasting servers, which I do, it tries to verify connection to it and comes back with the error:
'Login Failed: Please make sure entered the correct SQL server name and also use the right credential to connect to the SQL server.'
I can give the workstation any credential I like, including server\administrator with its password and this fails.
If I use a workstation with the CRM software installed and try to connect the 'address book' (which is the DB on the SQL server) it eventually times out and gives back the following error:
'The database is not support by this version of the CRM software.'
The CRM software has not been changed on the server or the workstations. The only changes have been the .NET updates for windows on July 12th, 2013. I feel that one or more of them made permanent changes to the SQL 2008 express install and it is not reversible by uninstalling those updates. So consequently it has modified the DB's associated with the SQL install.
So no matter how I try to come at it I cannot access the database or SQL server from workstations.
Once again, any thoughts on the subject would be greatly appreciated. Should I come up with a working solution myself (Going to attempt uninstalling SQL from the server and re-installing it again) I will post it here.