SOLIDWORKS PDM How to Reset the SQL ‘SA’ User Password

When using Microsoft SQL Server with SOLIDWORKS PDM, you will absolutely come across situations where you need the system admin password for SQL – upgrades, backups, running SQL queries, SQL card lists for data cards, etc. If you find yourself without the SA password recorded anywhere, you’ll be left stuck and unable to access a major component of your PDM system. This article is meant to walk through the two main ways you can reset your SQL password when you don’t already have it on hand.

Main Points

• Windows User Reset (Plan A)
• SQLCMD Reset (Plan B)
• Updating PDM with the New Password
• Preventing this in the Future
• Final Thoughts

Considerations

• You will need to log in to the SQL server with a local Windows Administrator account server to do this.
• Changing the SA password in SQL does not update the SQL login password fields in the PDM Archive and Database Server properties where the SA password is referenced. The PDM Archive and Database configurations will need to be updated manually.
• It is still necessary to have a secure Windows account with appropriate permissions to access SQL and change the SA password. Having that set up can prevent this sort of issue from ever coming up in the future. We’ll talk more about that at the end.
• Before attempting the reset, be sure to check all available resources. Ask your IT team and CAD Admins, and be sure to check any internal documentation or password vaults. It’s always better to find the password rather than reset it when possible.

Plan A: Windows User

This option assumes you have previously set up Windows users/groups for your SQL instance as alternative ‘sysadmin’ users. If that’s not the case, skip to Plan B.

1. Log in to the SQL server as a Local Windows Administrator.
2. Launch SQL Server Management Studio as an administrator. You can search SSMS from the Windows Start menu, right-click it, and select the Run as Administrator option.
   
   
   
   
3. Select Windows Authentication in the Authentication dropdown. Ensure that Trust server certificate is checked. Click Connect.
   
   
   
   
4. In Object Explorer on the left, expand Security > Logins.
   
   
   
   
5. Double-click the SA user to open the Login Properties dialog.
6. In the Login Properties dialog, delete any pre-filled credentials and type a new password in both the Password and Confirm Password fields.
7. Uncheck the Specify old password and Enforce password policy boxes if they’re checked.
   
   
   
   
8. Click OK to close the dialog.
9. Close SQL Server Management Studio and relaunch it. You want to confirm that the new credentials work as expected.
10. Switch the Authentication field to SQL Server Authentication and enter the new SA password you just created. 
    1. Tip: If you find you still cannot connect with the SA account after changing the password, you may need to go back into the Login Properties for SA and confirm the Login is set to Enabled under the Status section.
       
       
       
       
11. Once the SA password is successfully reset in the SQL Server Management Studio, the SQL login will need to be updated in the PDM Archive and Database Server Configurations. Head to Updating PDM With the New Password for the next steps.

Plan B: SQLCMD

If you don’t already have a Windows user or group defined as ‘sysadmin’ in your SQL instance, you will need to use the SQLCMD method to create a new sysadmin user for SQL. You can then log in with this new user and reset the actual SA password. This can be done by launching SQL in “single-user mode”. Then create a new SQL user with ‘sysadmin’ permissions using SQLCMD. This lets you to log in to SQL Server Management Studio and update the SA password.

1. Log in to your SQL server as a local Windows admin. Open Services by searching the Windows Start menu for “services”.
   
   
   
   
2. Stop all the following services, if present. Note that for the PDM server services, you’ll need to go onto the server they’re hosted on to shut them off if SQL is housed separately from your other components.
   • SolidNetwork License Manager
   • SOLIDWORKS PDM Archive Server
   • SOLIDWORKS PDM Database Server
   • SQL Server (MSSQLSERVER)
     • Be sure to turn off any other SQL Server services with different instance names. They should show as SQL Server (InstanceName).
   • SQL Server Agent (All Instance Names)
   • SQL Server Browser
   • SQL Server CEIP Service (All Instance Names)
   • SQL Server VSS Writer
   • TCP/IP NetBIOS Helper
     
     
     
     
3. Once these are turned off, open a Command Prompt window as an admin. You can do so by searching Start for “CMD” and selecting Run as administrator.
   
   
   
   
4. In the Command Prompt, type the following lines, hitting Enter between each line.
   1. 1. Note 1: These are case-sensitive! Be careful to match things up properly.
      2. Note 2: If you use a named SQL instance, you’ll switch out “MSSQLSERVER” with “MSSQL$InstanceName” (where “InstanceName” is the name of your SQL instance).

net start MSSQLSERVER /m”SQLCMD”
SQLCMD
CREATE LOGIN tempadmin WITH PASSWORD = 'RecoveryPassword123!'
GO
sp_addsrvrolemember ‘tempadmin’, ‘sysadmin’
GO
EXIT
net stop MSSQLSERVER
net start MSSQLSERVER

Let’s walk through what we just did:

• • net start MSSQLSERVER /m”SQLCMD”
    • This will start the SQL server in single-user mode, which only allows a single user/process to connect to it. We specifically only allow that single connection to happen through “SQLCMD”, which is a command-line utility for executing SQL. If you have a named instance of SQL, you would replace “MSSQLSERVER” here with “MSSQL$InstanceName”.
  • SQLCMD
    • This launches the SQLCMD utility.
  • CREATE LOGIN tempadmin WITH PASSWORD = 'RecoveryPassword123!'
    GO
    • This creates a user called “tempadmin” with the password “RecoveryPassword123!” and executes the command. We use this password to satisfy most complexity requirements, but depending on your environment, you could just use ‘password’ if you prefer.
  • sp_addsrvrolemember ‘tempadmin’, ‘sysadmin’
    GO
    • This adds the “sysadmin” role to the tempadmin user and executes the command. This role is what will allow us to edit the SA user.
  • EXIT
    • We exit the SQLCMD utility.
  • net stop MSSQLSERVER
    net start MSSQLSERVER
    • And finally, we stop the SQL service and restart it normally so it’s no longer in single-user mode.

Now that you've created a new sysadmin user, you can log into SQL Server Management Studio using those credentials to reset your SA user.

1. Open SSMS by searching for it from the Start menu.
2. Set the Authentication type to SQL Server Authentication, enter the tempadmin credentials, and click Connect.
   
   
   
   
3. Once logged in, go to Object Explorer on the left and expand Security > Logins.
   
   
   
   
4. Double-click the SA user to open the Login Properties dialog.
5. In the Login Properties dialog, delete any pre-filled credentials and type a new password in both the Password and Confirm Password fields.
6. Uncheck the Specify old password and Enforce password policy boxes if they’re checked.
   
   
   
   
7. Click OK to close the dialog.
8. Close and re-open SSMS. Log in with the SA credentials to confirm they work as expected.
9. Go back into Object Explorer > Security > Logins.
10. Right-click the tempadmin user you created > Delete. This ensures you’re not leaving a potentially unsecured system admin SQL user behind.
    
    
    
    
11. Go back into the Services menu and start each service you stopped at the beginning.

Now that you've updated the SA password and removed the old user, you can update PDM with the new credentials.

Updating PDM with the New Password

Once you’ve reset the SA password, PDM will need to be updated with the new credentials so the Archive and Database Services can properly integrate with the SQL database.

Archive

1. On the server hosting your archive service (if separate from the SQL server), open the Archive Server Configuration tool. This is located in the Windows Start menu under the SOLIDWORKS PDM folder.
   
   
   
   
2. First, you'll update the default server settings. Go to Tools > Default Settings.
   
   
   
   
3. Under the Login tab > SQL Login, click the Change… button. Enter the new SA password and click OK twice to get back to the main Archive Configuration screen.
   
   
   
   
4. Then, you'll update the vault-specific SQL login for each vault on your server. Double-click Archives.
   
   
   
   
5. Right-click the vault name > Properties.
6. Go to the Login tab and click Change… under the SQL login section.
   1. If the Use default SQL login box is checked, you can skip this step. The vault is using the server default password.
      
      

Database

1. On the server hosting your PDM Database Service (this is separate from SQL and could be on either the archive or SQL server if they’re separate), open the Database Server Configuration tool. This is located in the Windows Start menu under the SOLIDWORKS PDM folder.
   
   
   
   
2. In the Database Service dialog, enter the new SA credentials and click OK.

Once both the Archive and Database configurations are updated, you’ll want to go into the Windows Services menu (type ‘services’ into the Start menu search) and restart both the Archive and Database Services to apply the changes.

SQL Card Lists

If you utilize SQL Card lists, you’ll also want to go into the Administration tool under Lists > Card Lists and update the credentials there if you’re using the SA account to connect.

Preventing this in the Future

Once you’ve updated the password, you always want to be sure it’s stored somewhere safe. The intention is not to lose that again, but there’s always a chance something might happen. It’s best to prepare for these scenarios with a backup plan.

You can do so by adding Windows users or groups as sysadmin users to your SQL environment. Our recommendation is to create either a local admin on the server or to use a domain admin (or a domain admin group) so that you have multiple redundancy points available in the case of lost data, employee turnover, and the like.

To add new logins to SQL:

1. Go into SSMS > Object Explorer > Security > Logins.
2. Right-click Logins > New Login…
   
   
   
   
3. Ensure the login type is set to Windows authentication and click Search…
   
   
   
   
4. Enter the local admin or domain admin/admin group and click OK.
   
   
   
   
5. Go to the Server Roles tab and check ‘sysadmin’.
   
   
   
   
6. When done, click OK.

You now have a Windows user or group whose credentials will allow admin access to SQL in the event of a lost SA password. You’ll need to log in to the SQL server as that user and then use the Windows Authentication in SSMS to log in.

Final Thoughts

Keeping track of important admin logins for your environment should be a top priority. However, sometimes things happen, such as employee turnover, security events, data loss, etc. You still need access to your PDM database even in the event of a lost password.

We also have an article explaining how to reset a lost PDM Admin password, if you need help with that. If GoEngineer is your VAR, you can reach out to Technical Support if you have questions or require other assistance!

I hope you found this article explaining how to reset the SQL SA user password helpful. Check out more tips and tricks below. Additionally, join the GoEngineer Community to participate in discussions, create forum posts, and answer questions from other SOLIDWORKS users. 

Editor's Note: This article was originally published in June 2020 and has been updated for accuracy and comprehensiveness.

More SOLIDWORKS PDM Articles

How to Grant Users Attach Access Permission in SOLIDWORKS PDM

Changing the Default File View in SOLIDWORKS PDM

Link SOLIDWORKS File Custom Properties to Variables in PDM

SOLIDWORKS PDM Network Communication Troubleshooting

Managing Local Cache in SOLIDWORKS PDM

VIEW ALL SOLIDWORKS PDM ARTICLES