November 12, 2007 at 1:44 pm
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.
November 12, 2007 at 2:04 pm
Are you running Vista? If so have you run the user provisioning tool for SQL Server 2005 (included in the SP2 installation).
Tommy
Follow @sqlscribeNovember 12, 2007 at 4:37 pm
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
November 13, 2007 at 5:37 am
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?
November 13, 2007 at 5:50 am
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
November 13, 2007 at 5:52 am
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.
Regards..Vidhya Sagar
SQL-Articles
November 13, 2007 at 6:08 am
May 23, 2008 at 12:52 am
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