locked
Windows Login Disabled RRS feed

  • Question

  • Hi Experts,

    In one of our local servers having only windows authentication is not able to access now as the windows login is disabled in SQL server.

    How can i connect to server without loggin in as another user or administrator on that machine.??

    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Friday, July 30, 2010 5:51 AM

Answers

  • You are asking how to break security? not possible.

    If your login is disabled then you can't login. You have to use different user OR follow below

    Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

    Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

    http://msdn.microsoft.com/en-us/library/dd207004.aspx

     


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, July 30, 2010 6:22 AM
  • Hi Ratheesh,

     

    If we want to connect to SQL Server, we have to have a valid login existing in SQL Server.

    Is this machine is a local machine or remote machine?

    If it is a remote machine, I recommend that you contact the administrator of this database and ask for proper privilege to access SQL Server.

    If this is a local machine, we could use single-user mode to add a valid login to SQL Server and use this login to connect to SQL Server. For more information about Single User mode, you could refer to switch “-m” in this link: http://msdn.microsoft.com/en-us/library/ms190737.aspx

     

    In order to add a valid login to SQL Server via single-user mode, please follow the steps below:

    1.       Stop SQL Server service
    Run "SQL Server Configuration Manager" from "Start Menu | Microsoft SQL Server 2005(or 2008) | Configuration Tools"

    2.       Open "SQL Server Configuration Manager" from "Start Menu | Microsoft SQL Server 2005(or 2008) | Configuration Tools"

    3.       Click "SQL Server Services" in the left, and then double-click "SQL Server(instance name)"

    4.       In the dialog, click "Advanced" in the top, in the properties add ";-m" to the end of list in the "Startup Paramaters" option

    5.       Click "OK"

    6.       Start SQL Server service

    7.       Run "sqlcmd.exe"

    8.       Input the following script:
    USE MASTER
    CREATE LOGIN <login name> WITH <with option>
    GO
    sp_addsrvrolemember 'loginname', 'serveradmin'
    GO
    sp_addsrvrolemember 'loginname', 'sysadmin'
    GO
    EXIT

    9.       Stop SQL Server service, remove ";-m" you add in the step 4

    10.   Start SQL Server service

    11.   Connect to SQL Server with the login you created

     

    Note:

    ·         If you use an Operating System with User Account Control(UAC), please run "sqlcmd.exe" with "Run As Administrator"

    ·         There is no blank between ";" and "-m"

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, August 5, 2010 6:44 AM
    Tuesday, August 3, 2010 8:51 AM

All replies

  • You are asking how to break security? not possible.

    If your login is disabled then you can't login. You have to use different user OR follow below

    Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

    Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

    http://msdn.microsoft.com/en-us/library/dd207004.aspx

     


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, July 30, 2010 6:22 AM
  • Mine is having Vista..

    I tried as different user having local administrator privileage but no use


    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Friday, July 30, 2010 6:48 AM
  • Hi Ratheesh,

    If login is not created for your windows user, then you can not connect to sql server using windows authentication. You can ask any administrator to create a login for yourself.

    As, you are saying that you can log-in using other user's credential and then connect to sql server using that windows login. If that person is part of SecurityAdmin fixed server role, then you run follwoing query to crete your login.

    create login [<<DOMAIN\USERNAME>>] from windows;
    Use master;
    Create user  [<<DOMAIN\USERNAME>>]  for login [<<DOMAIN\USERNAME>>]
    GO;

    If using that person's login, you are not able to create a login and user, you can right click on SSMS shotcut and click RunAs and then supply that credentials.

    If you are using SQL 2005 and you are part of Administrator windows group then by default you get the rights to connect to SQL server 2005 as SYSADMIN fix server role. But, in 2008 you need to create a login.

    Hope this helps. 


    -Chintak
    Friday, July 30, 2010 8:00 AM
  • I can login to machine using another login but I am not able to connect to SQL server ...even though that user is a local administrator...
    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Friday, July 30, 2010 8:16 AM
  • If you are able to connect to SQL server using Local Admin then you can run follwoing commands after connecting to SQL for crearting login against your windows id.

    create login [<<DOMAIN\USERNAME>>] from windows;
    Use master;
    Create user  [<<DOMAIN\USERNAME>>]  for login [<<DOMAIN\USERNAME>>]
    GO;

    Note:- Here you need to replace the placeholder [<<DOMAIN\USERNAME>>] , with your login name.


    -Chintak
    Friday, July 30, 2010 9:28 AM
  • Hi Chintak,

    I am not able to connec to SQL server ....


    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Friday, July 30, 2010 9:30 AM
  • Ok,

    Then have a look at setps mentioned here and create your own login. This will work for sql 2005 ans sql 2008 as well

    http://beyondrelational.com/blogs/chintak/archive/2010/07/20/sql-2008-r2-new-installation-and-login-password-unknown.aspx

    Hope this helps.


    -Chintak
    Friday, July 30, 2010 10:05 AM
  • Did I miss something?

    What was the outcome of http://msdn.microsoft.com/en-us/library/dd207004.aspx


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, July 30, 2010 9:26 PM
  • Hi Ratheesh,

     

    If we want to connect to SQL Server, we have to have a valid login existing in SQL Server.

    Is this machine is a local machine or remote machine?

    If it is a remote machine, I recommend that you contact the administrator of this database and ask for proper privilege to access SQL Server.

    If this is a local machine, we could use single-user mode to add a valid login to SQL Server and use this login to connect to SQL Server. For more information about Single User mode, you could refer to switch “-m” in this link: http://msdn.microsoft.com/en-us/library/ms190737.aspx

     

    In order to add a valid login to SQL Server via single-user mode, please follow the steps below:

    1.       Stop SQL Server service
    Run "SQL Server Configuration Manager" from "Start Menu | Microsoft SQL Server 2005(or 2008) | Configuration Tools"

    2.       Open "SQL Server Configuration Manager" from "Start Menu | Microsoft SQL Server 2005(or 2008) | Configuration Tools"

    3.       Click "SQL Server Services" in the left, and then double-click "SQL Server(instance name)"

    4.       In the dialog, click "Advanced" in the top, in the properties add ";-m" to the end of list in the "Startup Paramaters" option

    5.       Click "OK"

    6.       Start SQL Server service

    7.       Run "sqlcmd.exe"

    8.       Input the following script:
    USE MASTER
    CREATE LOGIN <login name> WITH <with option>
    GO
    sp_addsrvrolemember 'loginname', 'serveradmin'
    GO
    sp_addsrvrolemember 'loginname', 'sysadmin'
    GO
    EXIT

    9.       Stop SQL Server service, remove ";-m" you add in the step 4

    10.   Start SQL Server service

    11.   Connect to SQL Server with the login you created

     

    Note:

    ·         If you use an Operating System with User Account Control(UAC), please run "sqlcmd.exe" with "Run As Administrator"

    ·         There is no blank between ";" and "-m"

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, August 5, 2010 6:44 AM
    Tuesday, August 3, 2010 8:51 AM
  • Thanks Tom for the Detailed response..

     

    My problem is i cant connect to SQL server..When i try using SQLCMD am getting error domain\username account is disabled..

     Msg 18470, Level 14, State 1, Server Line 1 Login failed for user '. Reason: The account is disabled.

    I tried using a different login like sqlcmd -U domain\user -P password..got the error

    Msg 18456, Level 14, State 1, Server , Line 1 Login failed for user .

     


    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Tuesday, August 3, 2010 12:56 PM
  • Hi,

    When you use -U and -P, you need to use sql authentication.

    In case of domain user you need to use -E switch and no need pass passoword for this.

    Also, if you are coonecting to remote sql server then you need to pass serverName after -S switch. But if server is installed on same machine, where you are trying this and you have default instance, then you can just type "SQLCMD" on command prompt and rest of parameter will be taken by default.


    -Chintak

    • Proposed as answer by Tom Li - MSFT Wednesday, August 4, 2010 1:00 AM
    Tuesday, August 3, 2010 4:38 PM
  • Thanks Everyone for the support...At last enabled the login succesfully..

    Logged in to the machine as domain \administrator

    stopped & started sql server in single user mode..

    Initially it throwed error that one one can login as in single user mode i stopped all sql related service(ssis,ssas,browser) when i tried sqlcmd..

    connected to sqlcmd(just sqlcmd was enough for me)

    then enabled the login..

    Once Again I thank everyone for the support


    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Wednesday, August 4, 2010 9:11 AM