June 27, 2012 at 6:15 am
Help please!
MS SQL server - Server crashed on 24 June and after restart I could not login under sa or other passwords. I think somehow the password has become corrupted
Normally I could change edit and change the sa password and gain entrance but connectivity issue(s) seem to prevent this.
I use SQL Server Enterprise Manager: There is a message under the icon in Enterprise Manager which says:
(connection failed, check SQL Server Registration Properties,)
The ASP.NET website running on the server gives the following error message (it has the sa password in the web.config file and needs this to be the same as the sa registation properties in the server to function- it was the same before the trouble started):
-----------------------
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. . . . . . . etc
-----------------------
Attempts with this to login to the server are greeted with the message:
"Please verify server is running and check your server registration properties (by right clicking on the <aias server name>) & try again"
The server is running (I have a green light on the icon button by the clock on my remote desktop- and all other indicators suggest this to be the case).
Right clicking and editing the registration properties brings the message:
"A connection could not be established to <aias server name>. Reason: Login failed for user sa, reason not associated with a trusted SQL Server Connection. Please verify SLL Server is running and check your SQL Server Registration properties (by right clicking on the <alias server name> mode and try again."
The registration properties are set to SQL Server authentication (in my Enterprise Manager interphase I have a choice of SQL Server authentication or windows authentication alone).
I have made a few searches of the web for some easy fixes but none seem to fit the bill. I am a complete novice so am unable to do anything fundamental for fear of making the situation worse.
One thing possibly irrelevant was an error in the application log right before the crash, this reads:
--------------
Exception information:
Exception type: CryptographicException
Exception message: Padding is invalid and cannot be removed. . . .
"
"
"
Request information:
Request URL: http://<the ASP.NET web address on the server>/scriptresource.axd?d=dfr5eeowuuwmrdoiola_-yqr9n6seaizuiohlvo5hhkcmmuev5l1xcspdgrv3bnvlu9zngdl3pnjtom26qzoxlhdc361r-aybmf3kzzutia1&t=633082823924061623
Request path: /scriptresource.axd
User host address: < IP address>
"
"
---------------------
The above error may not be relevant but I think it was very close to the time things went wrong (judged by activity logged in google analytics)
Can anyone please help.
June 27, 2012 at 6:35 am
Sounds like something has changed the authentication mode of the server from mixed to Windows only and forced a reboot of the service which wont allow you back in over SQL auth.
If you dont have admin access to SQL via a windows login, you will want to ensure your a member of local admins on that server and start SQL in single user mode allowing you to login and recongfigure it back.
Also dont use the SA account for your connection string, create an account which has the nessesary rights it requires to login to your SQL server. You leave yourself wide open using the sysadmin account, which is probably how this happened in the first place.
June 27, 2012 at 6:46 am
Did you check this article http://www.sqlservercentral.com/articles/Security/87159/
or this thread : http://www.sqlservercentral.com/Forums/Topic988899-149-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 27, 2012 at 8:30 am
Thanks guys I am relieved there appears to be some hope but I have a couple of questions:
(SSCrazy "Sounds like something has changed the authentication mode of the server from mixed to Windows only and forced a reboot of the service which wont allow you back in over SQL auth. If you dont have admin access to SQL via a windows login, you will want to ensure your a member of local admins on that server and start SQL in single user mode allowing you to login and recongfigure it back.")
1) I have an old SQL Server 2000 (version 8), will this method work with this version (the article referred above by ALZDB above relates to disaster recovery with SQL Server 2005 and 2008) .
If so:
2) I dont have access to SQL via a windows login- but have Administrator access to the whole server. Could you let me know how to
a) start SQL in single user mode
b) recongfigure it back
is it simple please describe expertise level required - I am complete novice.
June 27, 2012 at 8:32 am
Never tried single user on a 2000 server so cannot comment.
If this server is 2000 then assuming you havn't remove BULTIN\Administrators from the SQL logins or removed that groups SA rights to SQL, you should be to login and change the authentication mode and restart SQL.
This link should help http://msdn.microsoft.com/en-us/library/ms188670.aspx
June 27, 2012 at 8:37 am
arch .. you posted in a SQL2008 forum, hence the refs to it.
Anthony already provided the link to your solution 😉
/* SQL Server 2000 - sa password changes */
USE Master
GO
SELECT substring([name],1,10) as [Name], createdate, updatedate, sid
FROM master.dbo.syslogins
WHERE [name] = 'sa'
GO
btw: if you have builtin\admins disabled at sqlserver level, when you start sqlserver in maint mode, you'll still be able to grant the needed auth when on the box itself when you are in the windows admin group.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 4, 2012 at 9:48 am
Thank you for your help. I wanted to wait until it was fixed before replying.
The problem was that somehow the Login Mode for the MSSQL Server in the registry had changed from a '2' to a '1' meaning that windows authentication was required rather than SQL .
I cannot determine how this happened - was it someone changed it physically (?), was it a glitch with reboot? was it a failed windows update (particularly 'cos there was little physical space left on the hard drive following an automated backup) ? was it some other glitch? Any ideas?
Anyway however it happened- this little problem created havoc- with down time of 10 days whilst awaiting a specialist server administrator. The upside was that the stress worked wonders for my diet and weight loss 🙂 Once again thanks for your help and - and oops ! apologies for posting in the wrong forum :hehe:
July 4, 2012 at 10:29 am
Could of been SQL injection, especially if your using sa as the apps login.
All they need to do is enabled xp_cmdshell and enter a Regini.exe command via xp_cmdshell to change the registry.
But could of been anything really.
July 4, 2012 at 10:46 am
to change the login mode option, your sqlinstance needed to restart - which happened because of your crash - so when exactly the registry value has been set .... who knows may have been an easter egg sitting there for a while.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply