I forgot my SA password for my instance of SQL 2000

Hi all,

I have SQL 2000 and I forgot my SA password and cannot login as administrator using windows authentication.

Is there any way to reset SA password?

"Mixed Mode" authentication is enabled.

If I change authentication mode in register to Windows only and then after restarting SQL change it back to Mixed, will it reset SA password to "null"?

Thanks in advance.

If you log onto the machine with the Database server installed and use the local administrator account or a network administrator account you should be able to reset the password.

|||Just in addition to Glenns post and your qustion: It won′t reset the password to null with changing the authentication type back and forth. If you don′t have any GUI to administer your passwords you can use the procedure sp_password passing in a NULL for the old password parameter, as you don′t need the old if you are logged on as a user with sysadmin / securityadmin rights.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||As I said before I can't login even with local Administrator. I don't know what is going on. It is so weird.

I forgot my SA password for my instance of SQL 2000

Hi all,

I have SQL 2000 and I forgot my SA password and cannot login as administrator using windows authentication.

Is there any way to reset SA password?

"Mixed Mode" authentication is enabled.

If I change authentication mode in register to Windows only and then after restarting SQL change it back to Mixed, will it reset SA password to "null"?

Thanks in advance.

If you log onto the machine with the Database server installed and use the local administrator account or a network administrator account you should be able to reset the password.

|||Just in addition to Glenns post and your qustion: It won′t reset the password to null with changing the authentication type back and forth. If you don′t have any GUI to administer your passwords you can use the procedure sp_password passing in a NULL for the old password parameter, as you don′t need the old if you are logged on as a user with sysadmin / securityadmin rights.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||As I said before I can't login even with local Administrator. I don't know what is going on. It is so weird.|||

Try

sp_password NULL,NULL,'sa'

Assuming you never created password at the first place. parameter 1 is Old password, parameters 2 is new password, parameter 3 is user.

i created a login in SQL SERVER 2005 EXPRESS, but cant login...

create login dave with password='abc', default_database=tempdb

when i use this account to login my sql2005, i got

Msg 18452, Level 14, State 1, Server DAVID-398113F7C\SQLEXPRESS, Line 1
Login failed for user 'dave'. The user is not associated with a trusted SQL Serv
er connection.
why?You have probably installed the server such that it only allows Windows Authentication, so you cannot connect with a SQL login.

See the "How to: Change Server Authentication Mode" topic in Books Online for additional information on how to allow SQL authentication.

Thanks
Laurentiu|||follow the How to instruction, on right-click my sql server, i got a error msg. indicating that my server is not correctly registed !

in the reg. properity dialog box, i see my auth. mode is Windows Authentication, is there anything wrong with this ?|||Windows authentication means you can only connect with a Windows login. The login dave that you created is a SQL login, so it will not work if you only enabled Windows authentication. There is nothing wrong here, this is how it works.

However, the error message that you are getting when you right click on your server looks like a problem. You should post to SQL Server Tools General to find out what is wrong. If you cannot get an answer, please open a bug at http://lab.msdn.microsoft.com/productfeedback/Default.aspx.

In the meantime, you can try to change the authentication mode manually by modifying the following registry information to contain value 2 instead of 1:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode

1 means Windows authentication, this is the value you should find set. By changing this to 2, you'll set the mixed authentication mode. Be sure to restart the server for this change to take effect.

Thanks
Laurentiu|||thx! i changed the reg key, and it works fine now!

but, still, i cant get the server work, it popup a [SQL-DMO] erros|||Try posting on the SQL Server SMO/DMO forum with the text of the error and a description of the steps you took before you received it. There might be something wrong with your installation though. Have you tried installing on a different machine or reinstalling? Did you get any errors during the initial installation?

Thanks
Laurentiu

I changed my windows password and now I cannot connect to SQL Server on my development mac

I changed the login password on my computer today, and now I cannot connect to the SQL Server instance I have on that same machine. I am using Windows Authentication.

I would think that, if I can log on to my computer, I would be able to connect to the database, since I have been "authenticated" by windows but, that does not appear to be the case.

Can anyone offer any advice?

Thanks in advance for any help you can provide.

When i doubt, restart

|||

I figured it out.

If anyone is interested...

I had to manually go to Administrative Tools > Services and choose (double-click) SQL Server (MSSQLSERVER). Then go to the Log On tab and change the password there. I did the same for SQL Server Agent (MSSQLSERVER). I haven't done any of the other SQL Server - related services on my machine. I will do those as needed, I guess, cause I am not using them yet.

I hope this helps someone.

|||

Ah, you're using your Windows login to run the SQL Server service. Generally not a good idea on a server, for the reason you found, but it is probably okay on a development machine.

Glad you figured it out!

Don

|||

Ahh...Thats the start up account. So your SQL Server is set up to start as a "Local Account". Generally its a good practice to use a Windows account that is not part of windows admnistrator group. Check out books on line for more info about SQL Server agent and the security model.

sql

i can't login to sqlserver with a username and a password but works with NT integrated security

hi
i have created a username and a password in sqlserver 2000 from logins in Enterprise manager
and i permit him to the database i need to connect to ..
and i check all server roles for that user and i make sqlserver authentication for him with a password and then i goto the udl file to connect to that database using that user it fails !! and says

"login failed for that user 'myusername' reason not associated with a trusted sqlserver connection"

while i use NT integrated security it works well
so how can i connect to sqlserver using a username and a password

thanks in advance

Hi,

You need to make sure that SQL Server Authentication is set to "SQL Server and Windows Authentication mode". You do this by right clicking on the server name in Management Studio, selecting properties and then clicking on the Security page.

By default SQL Server is installed to use Windows Authentication mode only.

Cheers
Jeff

|||thanks alot man
you are goodsql

i can't login to sqlserver with a username and a password but works with NT integrated secu

hi
i have created a username and a password in sqlserver 2000 from logins in Enterprise manager
and i permit him to the database i need to connect to ..
and i check all server roles for that user and i make sqlserver authentication for him with a password and then i goto the udl file to connect to that database using that user it fails !! and says

"login failed for that user 'myusername' reason not associated with a trusted sqlserver connection"

while i use NT integrated security it works well
so how can i connect to sqlserver using a username and a password

thanks in advance

Hi,

You need to make sure that SQL Server Authentication is set to "SQL Server and Windows Authentication mode". You do this by right clicking on the server name in Management Studio, selecting properties and then clicking on the Security page.

By default SQL Server is installed to use Windows Authentication mode only.

Cheers
Jeff

|||thanks alot man
you are good

I cant deploy

Hello, I just did a report and it compiles ok, when I try to deploy then popups a window asking username and password, I put my username and password of active directory and it asks again and again, I am supposed to be an administrator on the report server.

What can I do?

When I try to connecto from sql management studio to reporting service Instance I got this error

TITLE: Connect to Server

Cannot connect to agamenon.

ADDITIONAL INFORMATION:

The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

OK

but the strange thing is that I can see the report manager

|||

Do I have to change something on the config file? or in IIS?

Here it is the config file

<ConnectionType>Default</ConnectionType>
<LogonUser></LogonUser>
<LogonDomain></LogonDomain>
<LogonCred></LogonCred>
<InstanceId>MSSQL.3</InstanceId>
<InstallationID>{fece13ec-acc5-4ebd-84bf-2534c30f2f7f}</InstallationID>
<Add Key="SecureConnectionLevel" Value="0"/>
<Add Key="ProcessRecycleOptions" Value="0"/>
<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>
<Add Key="DisplayErrorLink" Value="true"/>
<Add Key="WebServiceUseFileShareStorage" Value="false"/>
<!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump-->
<!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump -->
<!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump-->
<Add Key="WatsonFlags" Value="0x0428"/>
<Add Key="WatsonDumpOnExceptions" Value="Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException"/>
<Add Key="WatsonDumpExcludeIfContainsExceptions" Value="System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException"/>
<Service>
<IsSchedulingService>True</IsSchedulingService>
<IsNotificationService>True</IsNotificationService>
<IsEventService>True</IsEventService>
<PollingInterval>10</PollingInterval>
<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
<MemoryLimit>60</MemoryLimit>
<RecycleTime>720</RecycleTime>
<MaximumMemoryLimit>80</MaximumMemoryLimit>
<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://agamenon:90/reportserver</UrlRoot>
<UnattendedExecutionAccount>
<UserName></UserName>
<Password></Password>
<Domain></Domain>
</UnattendedExecutionAccount>
<PolicyLevel>rssrvpolicy.config</PolicyLevel>
<WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>
<IsWebServiceEnabled>True</IsWebServiceEnabled>
<FileShareStorageLocation>
<Path></Path>
</FileShareStorageLocation>
</Service>

|||Somebody from Microsoft that can answer me please? I havent been able to deploy a report.|||I just sawy IIS virtual directory configs and both have windows integrated authentication.

I can't change my sql login user's password in SQL Server 2005 Express

SQL Server 2005 Express keeps putting in a different password than the one I chose. I would check the properties on the login I want to change. Then I change the password and it gets accepted. When I try my web application, I get the dreaded "login failed for <loginname>". I look at the properties again and see my password never change. Is this a bug? I ever tried this syntax to no avail:

CREATE LOGIN <loginname> WITH PASSWORD='<mypassword>' CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

Can you provide some additional information, such as:

- what is the version of the operating system that you are working on?
- how do you check the login password - how do you know it was not set properly? what properties are you checking?

If you get a "Login failed" error, check the SQL Server errorlog as well. It should contain an error state, which can provide additional information on the cause of the error. Please post that error code so that we can determine if the failure is caused by using an incorrect password or some other reason.

Thanks
Laurentiu

|||

In addition to that, the error codes can span a various set of numbers, depending on the error got back. Even in the SQL Server 2005, there are many codes concerning the security features:
Look in here:
http://msdn2.microsoft.com/library/ms131024.aspx

HTH, Jens Suessmeyer.

I can't change my sql login user's password in SQL Server 2005 Express

SQL Server 2005 Express keeps putting in a different password than the one I chose. I would check the properties on the login I want to change. Then I change the password and it gets accepted. When I try my web application, I get the dreaded "login failed for <loginname>". I look at the properties again and see my password never change. Is this a bug? I ever tried this syntax to no avail:

CREATE LOGIN <loginname> WITH PASSWORD='<mypassword>' CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

Can you provide some additional information, such as:

- what is the version of the operating system that you are working on?
- how do you check the login password - how do you know it was not set properly? what properties are you checking?

If you get a "Login failed" error, check the SQL Server errorlog as well. It should contain an error state, which can provide additional information on the cause of the error. Please post that error code so that we can determine if the failure is caused by using an incorrect password or some other reason.

Thanks
Laurentiu

|||

In addition to that, the error codes can span a various set of numbers, depending on the error got back. Even in the SQL Server 2005, there are many codes concerning the security features:
Look in here:
http://msdn2.microsoft.com/library/ms131024.aspx

HTH, Jens Suessmeyer.

I cannot get Enterprise Manager to remotely connect!

I have an ASP page that is pulling data from an SQL server 2000 database. The code connects just fine using the specified Username and Password which was set up by the admin.

However, using that same username and password, I cannot get connected using Enterprise Manager SQL registration method so I can change database settings.

The error message I get says: SQL server does not exist or access denied (Conection Open(connect)).

The SQL server does exist (because it finds it in the server list). For some reason I am getting access denied. The server is running a mixed authentication mode.if sql2000 is running on win2003,it must be fix by sql service pack .
then you can settle the problem.|||I am trying to connec to a machine running win2000.|||Are you registering by machine name or ip address ? Can you ping the server ? When you say remotely - please provide more details on this ?|||Remotely means outside the firewall. I have also tried to connect inside the firewall. Inside the firewall, the server is listed by name in the available servers list when registering a new server. It just won't connect. Outside the firewall I am typing the IP in and nothing. SQL server is running on the webserver I am working off of.|||Can you ping the sql server (from inside or outside) ? Did you try using the ip address when registering from inside/outside ? What does your client network utility show as far as enabled protocols ? What are the errors you receive inside/outside ? Are you using the same machine to connect in both example (inside/outside) ?|||Also, since a firewall is involved - validate that the sql port is open. The default/normal port is 1433, but might have been changed by the sa/dba for security.