Can't change Server Authentication Mode

  • Hey all.

    I have SQL 2005 Developer installed on my machine, but when I try to log on under user 'sa' in the management studio I get.

    Unable connect to server. Reason: Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

    It only allows Windows Authentication mode, so I searched the Books online on Change Server Authentication Mode and it states to :

    1. In SQL Server Management Studio Object Explorer, right-click your server, and then click Properties.

    2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

    3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.

    Only problem is I can't get past line 1. since I do not have authorization under windows authentication to view propteries on the server. Basically my Windows user account has zero permissions and my SQL Authentication mode doesn't work.. am I screwed or what? I've resorted to using 2005 Express, but can't get Reporting services working properly.

    So my problem is this..either get SQL developer working right to use reporting soervices or get reporting services working in Express. Any help would be appreciated.

  • Are you running Vista? If so have you run the user provisioning tool for SQL Server 2005 (included in the SP2 installation).

  • In case it's not the Vista issue, SQL Server 2005 has a recovery mechanism for this type of scenario where you can't get in as an administrative user for SQL Server but you have administrative rights to the server itself:

    Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

    You aren't trying to reset the SQL Server password, but the same rules apply. Also, if you know your SQL Server is set for Windows authentication and you just want to change it, you can also attack it in the registry. Usual location in SQL Server 2005:

    HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode

    If the value is 0x00000001 (1), that means it is set for Windows authentication only. If you change the value to 0x00000002 (2), you're setting it for mixed mode. The next time SQL Server starts, it will start up in mixed mode. This is a known "backdoor" to get into SQL Server, hence the advice to always secure the sa account with a strong password, even if the SQL Server is set for Windows authentication only.

    K. Brian Kelley
    @kbriankelley

  • Thanks Brian,

    When I check it is set to 2 though, so apparently it's set to mixed mode. So what gives when I log on as 'sa' and I get the error:

    The user is not associated with a trusted SQL Server connection.

    I guess it could be a password issue? None of my standard passwords work. I installed this several months ago and have been using my express version, since I had problems from the get go. Is there anyway to reset the sa password?

    BTW, this is XP Pro SP2

    UPDATE: ... Apparently my Express version was under key MSSQL.1 and my developer version was under key MSSQL.4 since I'm getting a login failed for user sa now instead of the trusted connection error. Still without the proper password..I'm screwed?

  • Got it!

    Had to run a couple queries

    ALTER LOGIN sa

    WITH PASSWORD = ' '

    -- Since the account was diasbled

    ALTER LOGIN sa ENABLE

    Thanks all... hopefully I can get my report server working now

  • Hi Petey,

    In which version of SQL you are facing the error, developer edition or in sql express????

    Pls try the following

    To change the authentication mode to SQL

    1.) Change the registry value as said by Brain

    2.) Restart SQL services.

    3.) Try connecting with SQL authentication

    If you get the error "Login failed due to incorrect password", then try the below steps.

    FOR Developer Edition

    In command prompt do the follwoing

    1.) sqlcmd -E -S. -A press enter

    2.) sp_password null,your_new_sa_password,'sa' press enter

    3.) go press enter

    Then try connecting with the new password

    FOR SQL Express edition

    By default DAC is disabled in SQL Express, hence

    1.) Add -T7806 flag in SQL startup parameter to enable DAC, restart SQL Express

    2.) Do the steps mentioned for Developer edition

    Try with the new password.

  • Hey All,

    It was quiet easy at my side as I use Vista.. I used the same method that was mentioned in SQL Server BOL.. But once re-started the system, I am able to use login thru SQL Server authentication mode 😉 .. It worked 🙂


    Regards,
    Genie Cool

  • Thank you very much.

    I just hanged in the morning with the same issue and here is what worked for me..

    1. Change the Authentication to Mixed Mode using RegEdit option

    2. Using SQLCMD connected with windows authentication

    3. Alter login 'sa' enable with default_database=master

    restarted the sql services

    and this works great..

    hanks for all the information here.

    Pawan Bansal

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply