SQL Server does not exist or access denied - A real doozzy and not your standard problem!!
Hi ,
I am running SQL Server Desktop Engine on Windows XP. I am developing in .NET framework v1.1.
I intend to u/g to the express edition 2005 however should this old version of the engine be OK (particularly considering that Windows updates are being often run)?
Main question.
For ages the database has worked well then one day recently (just after running a Windows update) the following error occurredSQL Server does not exist or access denied .
I followed my code and this is the result of an exception being thrown in my code due to one of two obvious possibilities.
Everything looks fine as far as SQL Server DEsktop Engine (including services and TCP/IP protocols. Yes I have check cliconf.exe and svrnetcn.exe !!)
I specified mixed mode access (at setup) and In .NET my connection string is :
ConnectionString = "Persist Security Info=false;IntegratedSecurity=sspi;Database=ShedDb;server=warehouse1";
I notice that when I try to login to the database engine with OSQL it does not recognise the original sa password?
Somehow this has been changed (I definitely have not)
Q1. Is it possible that a virus (Worm) could have done this?
Q2. I think that I am stuffed(sorry) because now the sa has changed I cannot perform any admin functions (like resetting the sa pw) Is there any way to change the sa password in this sitatuon?
Q3. The database is locally setup on my development system. When I use the above mentioned connection string I assume that because I do not mention a userid and password that database access is being achieved via Windows authentication (I did specify mixed mode at setup) Is this correct?
Q4. I can appreciate the severity of the sa password being altered. However if Windows authentication is being used by my application, why should this matter? How does Windows authentication work?
Q5. I do have a backup of all the databases including master, model, temp... (I think that the sa password is stored in master). Would the crude approach of simply copying the backup of these backup database files (like master.mdf and master.ldf) suffice in fixing this problem?
Any help would be appreciated.
Thanks
Andrew.
[2341 byte] By [
Insignia] at [2007-12-24]
Q1. Is it possible that a virus (Worm) could have done this?
Yes, sure. Don′t know of any one right now. But programmiung a worm to do a dictionary attack shoudl not be too hard.
Q2. I think that I am stuffed(sorry) because now the sa has changed I cannot perform any admin functions (like resetting the sa pw) Is there any way to change the sa password in this sitatuon?
If you have enabled Mixed or Windows authentication log on with an administrator or someone being in the sysadmin SQL Server role (By default local administators whereas also domain administrators belogn to the group). Then use the command sp_changepassword 'sa',NULL,'New Password' to reset the password. You won′t need to put in the old password as you are the sysadmin.
Q3. The database is locally setup on my development system. When I use the above mentioned connection string I assume that because I do not mention a userid and password that database access is being achieved via Windows authentication (I did specify mixed mode at setup) Is this correct?
You stated the "IntegratedSecurity=sspi" which mean that you want to use the Secure Service Provider Interface, which will lead you to integrated authentication.
Q4. I can appreciate the severity of the sa password being altered. However if Windows authentication is being used by my application, why should this matter? How does Windows authentication work?
http://win32.mvps.org/security/sspi.html Windows Authentication should be your prefered authentication type as it integrated directly in your existing domain infrastructure.
Q5. I do have a backup of all the databases including master, model, temp... (I think that the sa password is stored in master). Would the crude approach of simply copying the backup of these backup database files (like master.mdf and master.ldf) suffice in fixing this problem?
Well, simply reset the password if you have Windows Authentication enabled, thats all. :-)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Thanks very much Jens,
Q1. Because it is a development computer, it may seem mad however at install time I only specified a password for the sa user. I did not setup any other user. Using OSQL I suspect that I cannot use Windows type users. Correct? If not then how may I login in order to run sp_changepassword?
Q2. I skimmed over the url on SSPI - thanks. Does Windows authentication require that the user knows the sa password? This seems wrong.
Q3. Put another way, if I was specifically using the sa userid and password in my .NET connection string, I could understand why the connection failed if the sa password had been altered?
Q4. As I am specifying sspi authentication, why should the sa password matter. As the data blocks are moved around in the sspi process, is any password exchanged?
Andrew.
Q1: "Using OSQL I suspect that I cannot use Windows type users. Correct?" Yes you can. Just specify the -E switch.
Q2: "Does Windows authentication require that the user knows the sa password? " No, WINdows authentication uses the Windows user token (the user was already authenticated by Windows) and will check the autorization at SQL Server. The user does not need to provide (and even cannot provide a password)
Q3. You will have to remove the Integrated Security Part. You either can use the UserId/Password combination OR the integrated authentication thing. Both is not possible.
Q4: No password is exchanged.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
