How to Reset the SA Password
SQL Management Studio
Written by: Bryce Hooper, Elite Application Engineer
There are numerous enough reasons that we may need to change our passwords. Staff changes, general security, in the aftermath of a breach… Whatever the reason, we’ll help you with how to change it and where we need to change it beyond SQL for SOLIDWORKS products to continue working.
Changing the SA password in SQL Server Management Studio
Changing the SA password in the SQL Server Management Studio (SSMS) is the easiest method to do and can be done through the user interface. Log into SSMS with the current SA password. If you don’t have that password, you can try using Windows Authentication. We won’t know without logging in, but if a user was designated as an administrator on the install of SQL, they will be able to log in and change the password of SA. If we don’t have the current SA password, and we don’t know a valid user account, you can skip down to the last section on getting in when we have no known credentials. Then return here when you’ve logged in.
Once logged in expand the Security folder in the Object Explorer. If you don’t see it, you can turn it on through the View menu. From there, proceed to do the same with the Logins folder. Here we see all valid users that can log into the SQL server. We’re concerned with SA for now, so we will right click and select properties.
From here we can control most aspects of the SA user. To change the password, we just change the Password field and match it in the Confirm Password field. Click OK and the system will process the change. Something to note is that, by default, the password will need to meet the criteria set by your Windows environment.
SA User settings
There are a few changes that can be made and some things that we may need to check in the event we’re having issues with our SA user (or PDM from its use of the SA user).
On the general page, if we so choose, we can decide to deviate from the rules of the Windows password policy. The checkbox to turn this off is on the General page (shown above). If we would deem it appropriate, we can also enforce the password expiration policy.
NOTE: This is not recommended in this type of environment. There will be no warning when PDM stops working if the expiration date is gone passed without password updates. The vault will just stop working.
The status page is where we can see whether the SA user is enabled for login (important that it be turned on). If we have a password policy that will lock users out on bad password attempts and we are enforcing this policy (on the general page) then we can experience an SA user lockout. The last checkbox, that is grayed out in the screenshot above, will be checked. Sometimes, depending on the policy, the user can be unlocked automatically. With a PDM system that relies on this user, we can’t always wait. Clearing the checkbox and hitting OK will unlock the user.
Changing the SA password in a PDM system
Once the password is changed in SSMS, we need to change it everywhere that it is used. In PDM, this will be in two places.
- PDM Database Server Service
- PDM Archive Service
Changing the Database Server is simple to do.
Launch the Database Server Configuration. The dialog that shows has a place for the SQL user and password. We aren’t changing the user this time, just the password. Update the field and click OK. To confirm that its working with this new password, restart the SOLIDWORKS PDM Database Server Service in the Windows Services dialog.
Updating the Archive configuration can be done in a few steps. The exact number of steps is dependent on how you have your vaults configured. The archive is either configured so that all vaults running on the server have the same defaulted settings, or that each of them have their own set of settings.
To start, make sure that the default settings are using the correct password. This will make things right for any new vaults that are created later and if our vaults are all configured to use the default. Launch the Archive Server Configuration tool through the start menu. If it doesn’t appear to launch, check the task tray by the clock to see if it is already running. If it is, right click and select open. In the dialog, go to Tools > Default Settings. Click the Change button towards the bottom under SQL Login. In the resulting dialog, provide the new password twice and click OK.
Our next step is to check how each vault is configured.
We do this by selecting the root folder and then right clicking a vault. Go to properties to get the dialog shown above. If the vault is configured to use the default settings that we’ve already corrected, the checkbox “Use default SQL Login” will be checked. Otherwise, click Change under the SQL Login header and change the password. Repeat this for all vaults.
No Credentials? No problem.
In the situation where there is no known login that is working, there is still an option to log into the system. This is a little more of an involved situation and is best done when all client machines can be turned off. We will need to put SQL into Single User Mode. I’ll detail the steps here as they are typically followed, but more information can be found here from Microsoft.
- We will need to start by logging directly into the machine hosting the SQL server. Normally we can do most things with any machine that has SSMS, but not in this case.
- We should then open the Windows services on the server. We will be stopping the SQL Server service along with any other SQL services (SQL Server Agent, SQL Server Browser… etc) to avoid issues.
- It is also a good time to make sure that no other machines try to access the SQL server. To make certain this doesn’t happen, start by shutting down the Archive Server service and PDM Database Server service. If any other service connects to SQL while we go into Single User Mode, our one connection will be consumed.
- Unless specified differently during the installation of SQL, the default instance name will be MSSQLSERVER.
- Once the server service has been stopped we can enter some specific start parameters for our next restart of the service. To get into single user mode, we want to enter ‘-m’. We can then hit Apply and start the service again.
- Right Click and Open SQL Server Management Studio by selecting Run as Administrator. Log in using Windows Authentication. If no other connections have been made to the server, we will be able to log in. From here, we can follow the steps above to change the SA password to a known value. We can also add other users with valid Windows Authentication.
- When done with creating/changing users, stop the service and remove the ‘-m’ from the start parameters. Click Apply and start the service for a final time.
- If we shut down the PDM applications that would access SQL, we can restart them at this time.
- Restart the other previously shut down SQL services. If we aren’t certain which ones need to be restarted, it may be simpler to restart the machine. Necessary services will restart as normal on the next reboot.
- Now that we have access to the SQL server with a valid login, don’t forget to change the password stored in the Archive and PDM Database server services.
Before taking any steps outlined in this article, make backups for all database files. DASI is not responsible for any damages resulting from the misuse of this document.