Enterprise PDM Upgrade, Lost 'sa' Password.

In a typical installation of Enterprise PDM, sysadmins or responsible users are also installing a new instance of Microsoft's SQL Server.  During the initial install there is a step in the process that requires an 'sa' password to be set when running in mixed mode.  This is the "System Administrator" account and due to it's importance to the security of the SQL server, there are some prequisites to setting this password which tend to make it longer than most sysadmin's "usual" password and hard to remember.

Problem:

Once the 'sa' password is set, and entered into the setup screens of EPDM, it is remembered by EPDM and continues to use it for communication.  Since this password is somewhat unique to this installation, companies then to "misplace" this password and panic when they have to enter it for an EPDM upgrade.  This typically involves a phone call to me frantically asking if I happened to write it down, at which point I say NO!

Solutions: A few...

GUI Option: Since MSSQL is setup in "Mixed Mode" you can also log into SQL Server Management Studio with the local Windows administrator account or an account with membership to the BuiltIn/Administrators group. Once authenticated via Windows an admin can go into the Object Explorer under Security->Logins->sa properties and reset the 'sa' password and resume the upgrade process.

SQL Script Option: If you rather use an sql script in Management Studio, click on "New Query" and type the folling script and execute:

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
Go
ALTER LOGIN [sa] WITH PASSWORD=N'yournewpassword' 
GO 

Command Line Option: As long as you can login with this Windows administrator account, why not fire up the command prompt and run a quick script. Just go START->RUN->CMD and type (hitting ENTER after each line):

OSQL -S yourservername -E
1>EXEC sp_password NULL,'yournewpassword','sa' 
2>GO 

You can type 'exit' at the '1>' prompt to exit to the command prompt and 'exit' once again to exit the command prompt.

Gotchas...

MS SQL does give you two ways in when you use Mixed Mode authentication, however if you forget your sa password and mess with the domain or local accounts of that system, recovering this data is much harder to do and requires last resort registry hacking and/or 3rd party tools to recover this data.  So remember your 'sa' password and know where to get it when upgrading EPDM or other tools that require 'sa' authentication. ~Lou