Can't get in to SQLServer 2005 express with a Privileged account

Bummer. I can't remember the SA password. I had setup a user account, but I can't change anything or add any new accounts using this login. I can't get in using the windows authentication method no matter how I am logged into this machine.

Any suggestions? I have never been able to use Windows Authentication. There must be something I'm missing here. I have spent hours and hours trying to get into this machine. I just want to replicate a database. This is very frustrating.

Thanks guys.

How are you connecting to SQL Server? On Express edition, Windows authentication should always work even if you are non-admin.

You can also try using Windows Authentication with a user account that is a machine admin.

Alternatively, members of this Windows Group: SQLServer2005MSSQLUSER$<machine_name>$<instance_name> should also have access. Please replace the <machine_name> and <instance_name> variables with the appropriate values for your installation. So if you add your Windows user to this group, you should also have access.

What error message are you seeing?

Sung

|||

It was telling me that I couldn't access the default database. I found where someone said to do:

sqlcmd -E -d master

That allowed me to get the SQLCMD prompt. I didn't know what to do after that. I found in the SQL Server 2005 Administrator's Pocket Consultant that you can make any user an administrator by issuing the command:

sp_addsrvrolemember 'login', 'sysadmin'

That did the trick. I was able to elevate the one login that I knew to sysadmin status. From there I had full access and privileges.

Thanks for your response.

Cant connect via EM after changing sa password?

A client of mine changed his SA password "because it hadn't been changed in a while". :mad: Now nobody can get into Enterprise Manager - opening EM or trying to right-click > Connect on the server yields an error:

A connection could not be established to SERVER1.

Reason: login failed for user 'sa'.

Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.

Of course, if I right-click on the SERVER1 node, I get the same message.

My client remembers the password he set; I managed to use it to get into Query Analyzer, which I thouhght was weird. But no luck with Enterprise Manager. It's just trying to log in as sa without prompting for a pw.

I already tried running sp_Password in Query Analyzer to change the pw back to what it was. Still can't connect to the SQL Server via Enterprise Manager.

What can I do?Did you edit the sql server registration and change the password there?

EDIT: And while I'm Here

Now nobody can get into Enterprise Manager

SCARY

Sorry Rudy|||Unclear from your post: have you tried editing the connection properties, or are you not able to get to that point?

If it won't even let you right-click on the registration to edit the properties, then try dropping the registration and creating a new one.|||Unless BUILTIN\Administrators local group is removed from SQL Server security, you can logon as a local admin or as a member of that group and easily change the SA password.

Cant connect to MSDE on XPSP1

We are writing a setup program for a customer that requires a MSDE
installation.
Using the following flags for the MSDE setup.exe:
SAPWD = "password" SECURITYMODE=SQL
The setup complete successfully, we are restarting the installed machine
(SQL services restarts as well)
But we cannot connect to the MSDE from any enterprise manager (Enterprise,
Developer versions)
This happens only when the MSDE installed on a WindowsXP SP1 machine.
Can someone help us solve this problem?
Thanks,
Shif
It may be that you don't have any network protocols enabled for MSDE. Run
the tool svrnetcn.exe (you'll find it in C:\Program Files\Microsoft SQL
Server\80\Tools\Binn) and check to make sure that you have the necessary
protocols enabled.
Jim
"Shif Arad" <shif@.inter.net.il> wrote in message
news:Om8nd%23vJEHA.2604@.tk2msftngp13.phx.gbl...
> We are writing a setup program for a customer that requires a MSDE
> installation.
> Using the following flags for the MSDE setup.exe:
> SAPWD = "password" SECURITYMODE=SQL
>
> The setup complete successfully, we are restarting the installed machine
> (SQL services restarts as well)
> But we cannot connect to the MSDE from any enterprise manager (Enterprise,
> Developer versions)
> This happens only when the MSDE installed on a WindowsXP SP1 machine.
>
> Can someone help us solve this problem?
> Thanks,
> Shif
>

Cant connect to MSDE on XPSP1

We are writing a setup program for a customer that requires a MSDE
installation.
Using the following flags for the MSDE setup.exe:
SAPWD = "password" SECURITYMODE=SQL
The setup complete successfully, we are restarting the installed machine
(SQL services restarts as well)
But we cannot connect to the MSDE from any enterprise manager
(Enterprise,Developer versions)
This happens only when the MSDE installed on a WindowsXP SP1 machine.
Can someone help us solve this problem?
Thanks,
Shif
Verify that the MSDE is listening on TCP/IP. Check the NT Application logs
after MSDE is started.
If it's only listening on Shared Memory, then only local connections will
succeed.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
How can i change this option AND in what menu should i look for it ?
Thank
Javier A. Junca Barreto.
Desarrollo de Sistemas
SICyA Software.
Web: www.sicya.com
Mail: desarrollo@.sicya.com
Tel: (57 1) 272 0738
|||MSDE doesn't ship with any utilities. So, you could change this via the
registry.
Check the following registry key on your server:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSDE Name Here
\MSSQLServer\SuperSocketNetLib
Add np and or tcp to the ProtocolList value.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Cant connect to MSDE on XPSP1

We are writing a setup program for a customer that requires a MSDE
installation.
Using the following flags for the MSDE setup.exe:
SAPWD = "password" SECURITYMODE=SQL
The setup complete successfully, we are restarting the installed machine
(SQL services restarts as well)
But we cannot connect to the MSDE from any enterprise manager
(Enterprise,Developer versions)
This happens only when the MSDE installed on a WindowsXP SP1 machine.
Can someone help us solve this problem?
Thanks,
ShifVerify that the MSDE is listening on TCP/IP. Check the NT Application logs
after MSDE is started.
If it's only listening on Shared Memory, then only local connections will
succeed.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
How can i change this option AND in what menu should i look for it ?
Thank
Javier A. Junca Barreto.
Desarrollo de Sistemas
SICyA Software.
Web: www.sicya.com
Mail: desarrollo@.sicya.com
Tel: (57 1) 272 0738|||MSDE doesn't ship with any utilities. So, you could change this via the
registry.
Check the following registry key on your server:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSDE Name Here
\MSSQLServer\SuperSocketNetLib
Add np and or tcp to the ProtocolList value.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.