May 6, 2022 at 9:09 am
Hi.
windows authentication administrator and SQL authentication SQL user both are not able to login, please suggest How to login SQL instance? "sa" account was disabled after SQL server installed.
>sqlcmd -S localhost -U sa -P <password>
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'..
>sqlcmd -S localhost
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'Hostname\Administrator'
thanks
May 6, 2022 at 9:28 pm
So this is a problem because:
A - sa is disabled, so you cannot log in as sa.
B - local machine administrator account is not an admin
What I would suggest is to reach out to one of the users who IS an admin on the system and have them run the command you need. IF your instance is set up to use an AD account for the service, it is possible that account is an admin and you could log in as that account and connect. Failing that, you are going to need to do some disaster recovery which will require downtime of the SQL instance. Quick google brought me to this article from Microsoft which explains how to do it:
BUT you must be an admin on the server hosting the SQL instance, but it looks like you are since you are running the command prompt as administrator.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2022 at 6:23 pm
B - local machine administrator account is not an admin
When we have a new server (aws so we create and delete them fairly often) and I am a local admin, but have not been granted sql access, I can rdp to the server and open ssms as admin, then add myself. Is this a possibility?
May 7, 2022 at 10:21 pm
When we have a new server (aws so we create and delete them fairly often) and I am a local admin, but have not been granted sql access, I can rdp to the server and open ssms as admin, then add myself. Is this a possibility?
Interesting... what does your SQL Server Licensing look like for those servers?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2022 at 5:20 am
Hi.
Let me explain. actually SQL and OS installed from template in new host.
if new server commissioned just applied template in new host. after that assigned new host name and new IP address.
but SQL installation done by another server name without domain also added existing hostname/administrator in SQL server at time of template creation .
once deployed template that new server then new server brought into domain group. so new server Administrator not logged.
I tried you mentioned URL link and brought into single user mode but no hope.
Thanks.
May 9, 2022 at 4:52 pm
When you say "brought into single user mode but no hope" what do you mean? You got errors?
Now, like Jeff said, I am a bit curious about the licensing on that as I would expect that spinning up a new SQL instance and OS from a template would not be cheap on AWS as you would need to license both the OS, the SQL instance, and the resources on AWS.
It also sounds like you have a process in place already for doing this. I suspect that process is tested and documented? May not hurt to double check that process just to make sure you didn't miss a step by accident.
EDIT - that is assuming AWS... if it is some other VM infrastructure, I imagine there is still licensing costs there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 10, 2022 at 5:41 am
Hi Mr. Brian Gale. Thanks a lots for sharing link. It is working as expected and SQL server connected as expected. once again thank you..! 🙂
Step 1: SQL instance changed to single user mode then executed as below PowerShell commands
Step 2:
PS C:\Users\Administrator> $strong_password = ""
PS C:\Users\Administrator> sqlcmd.exe -E -S<Hostname> -Q "CREATE LOGIN TempLogin WITH PASSWORD = '$strong_password'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
PS C:\Users\Administrator>
PS C:\Users\Administrator>
PS C:\Users\Administrator> sqlcmd.exe -S<Hostname> -UTemplogin -Pj8:zG=J?E9
1> quit
PS C:\Users\Administrator>
Step 3:
SQL instance changed to multi user mode then restart SQL service and connect SSMS by using TempLogin.
Thanks
May 10, 2022 at 1:11 pm
I would strongly encourage you to edit your post and take out the passwords you put in there... you should never post a password in plain text on any forum, even if the login has been removed/disabled. It is just a very bad practice to write your password down anywhere that is not encrypted - password managers for example SHOULD encrypt your data before saving, but this forum presents the password in plain text, as is apparent since we can all read your password...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 20, 2022 at 8:44 am
During the SQL Server installation, you must have allowed at least one Windows user to have access to SQL Server and be able to log into SQL Server using this account. If this has not been done, you will need to reinstall SQL.
Detailed Steps
SQL Database Recovery Expert 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply