Don't know SA password for SQL 2000 (currently using windows authentication)

gleasoninc
gleasoninc used Ask the Experts™
on
We are using an old SQL2000 server running Windows Server 2003 as a database for our Blackberry server.  We don't the sa password, or even if we have one.

We have to upgrade the blackberry server, and it requires us to take off MSDE 7 and put on MSDE 2000 on our blackberry.

We took off the MSDE7 and are trying to install the MSDE2000, but it won't install because it says it needs a strong SA password.

I checked the SQL2000 and it is using Windows authentication in the Enterprise Manager (Edit SQL Server Registration Properties), so I thought maybe we didn't have an SA password.

However, when typing osql -U sa and pressing the enter on the password, I get the message:  Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

So does this mean we don't have an SA password?  If we have one, how can I find out what it is, or change it using windows authentication?  If we don't have one, how can I change it?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Make sure you see this "To connect by using Windows authentication, type this command:  use osql -E"
under How to change your SA password

Author

Commented:
That's the one I was using. A couple of probs, it's for 2005, not sure if this matters?

Also, the instructions to change it indicate that you either have to know it (which we don't) or press the enter and get attached (if it's blank).  But we can't press the enter and get attached since it give us the error halfway down the page:

"The following error message indicates that the computer that is running SQL Server is set to Windows Authentication only:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

so we can't go forward with the document since we can't get the attachment.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

If I understand you correctly the problem is in your installation of MSDE2000.  It doesn't have an sa password (you must give it one)

run the install for MSDE with the following parameters after it

setup.exe INSTANCENAME="BLACKBERRY" SECURITYMODE=SQL SAPWD="mystrongsqlserverpassword"

obviously replace BLACKBERRY with whatever you want to name this instance of SQL Server, and replace mystrongsqlserverpassword with a strong password  That being mixed case with a few numbers also if you want it strong.  Be sure to remember it or write it down and lock it up. If you forget it you will be in trouble.

This should install an instance of MSDE2000 on that machine with an sa password of whatever you specified above.

You will then need to load the database, setup permissions or whatever.

HTH,

Shane

Commented:
Yes, if you have the MSDE 2000 set up for Windows authentication only, then the SA is not a trusted connection.

If you want to use the SA account, you will need to change the authentication from Windows to SQL Server and Windows.

Author

Commented:
Thanks Dxpert - we are right on the same page, because I tried this too.  It says:
"'use' is not recognized as an internal or external command, operable program or batch file"

I figured that was because the instructions were for SQL2005, but maybe I'm doing in wrong place?  I was doing from command prompt.  Maybe should do from inside SQL?

Commented:
http://www2.cs.uh.edu/~zhenzhao/Links/Interesting/MSDEPassword.htm this one is for SQL2K but I think it's basically the same thing.

Commented:
It says command prompt window but I'm not sure, I can't test it from where I am. Does the SQL2K has a command prompt utility?

Author

Commented:
Many, many thanks to all.  The MSDE loaded on the blackberry with the command from SSTORY as
setup.exe INSTANCENAME="BLACKBERRY" SECURITYMODE=SQL SAPWD="mystrongsqlserverpassword"

Blackberry is working, not sure if SQL accepted the password, will have to continue to investigate that, but got the MSDE2000 loaded and the Blackberry putting out traffic again, which was my emergency situation.

Thank you!
I ALWAYS suggest loading in securitymode=SQL  This allows you to use either SQL accounts or trusted Windows accounts.  If you do the default, you can only use trusted Windows accounts.  In my mind their might be times when a network admin has no business being a SQL Server admin, but Microsoft doesn't see it that way.  Putting it in SQL mode allows the most flexibility.

Thanks for the points!

-Shane

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial