Post reply

Login failed for user windows user and SQL user

  • May 6, 2022 at 9:09 am

    #4027902

    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

    #4028337

    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:

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15

    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

    #4028732

    Mr. Brian Gale wrote:

    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

    #4028805

    Ed B wrote:

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 9, 2022 at 5:20 am

    #4029278

    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

    #4029560

    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

    #4029850

    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

     

     

    • This reply was modified 1 year, 11 months ago by  SQL Galaxy.

  • May 10, 2022 at 1:11 pm

    #4030046

    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

    #4036073

    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

    1. Open SQL Server Management Studio by going to Start > Programs > Microsoft SQL Server YEAR > SQL Server Management Studio.
    2. Log in with Windows Authentication with an administrative account.
    3. Change the authentication

      1. On the Object Explorer window right-click on the server name and go to Properties.

        1. Select the Security section. Under Server Authentication change the selection from Windows Authentication mode to SQL Server and Windows Authentication mode.
        2. Click Ok

      2. Click Ok

    4. Enable the sa login:

      1. In Object Explorer expand the Security folder and then Logins.
      2. Right-click sa and go to Properties.

        1. On the General tab set the password you want to use.
        2. On the Status tab, in Login set it to Enabled.
        3. Click OK

    5. Restart SQL Server:

      1. Right-click on your server name in Object Explorer
      2. Select Restart

    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