A client recently asked me to try and upgrade their system without using the “sa” account. They had a legacy app that had the sa password hard coded and they could not change the password for the GP upgrade, and they did not want me to have the password.
They, instead, gave my windows account sysadmin rights on the server, so I had to get creative to be able to upgrade GP without the “sa” account.
First, let’s talk about GP’s password encryption. The only way you can login to GP is with the DYNSA or the sa account, UNLESS you create the account inside of GP and then you can login using that account. The DYNSA is GP’s built in administrative accounts, and also owns all the GP databases in SQL. The “sa” account is the built in sysadmin account for SQL Server.
In today’s SOX world, many companies often want you to disable the “sa” account. Some even go as far as renaming the “sa” account to a different name so it doesn’t exist at all. CRAZY, but necessary in today’s world of things being hacked constantly.
GP encrypts the passwords of the SQL accounts you create inside of GP so that the users can not use SQL Management Studio or other tools to access the database – in essence, their user account can ONLY be used for GP.
Fortunately, they gave me the DYNSA account, so I was able to login to GP using that account and gain full admin rights. However, DYNSA does NOT have the necessary permissions in SQL to be able to create database, so when you run GP Utilities to run an upgrade you get an error:
“This account does not have administrative rights in SQL”
No problem! Just go into SQL Management Studio, go to security, and locate the DYNSA account and right click and select “Properties” and then click on the server roles and check the box next to “Sysadmin” and click OK:
Note: if you are not upgrading GP and you launch GP Utilities as DYNSA without sysadmin permissions you will only see one option, update modified forms and reports:
Easy to fix, just give sysadmin permissions.
Note: this workaround is NOT Microsoft approved. However, it does work successfully and I’ve performed a couple upgrades now doing this without any issues.
Also, it is possible to create a normal GP user, give them power user, and then add them to that sysadmin role in SQL and they will also be able to run GP utilities, create companies, delete users in GP, and upgrade your system.
Be careful who you give the sysadmin rights to in SQL, they have exactly the same permissions as sa and they can delete databases, shut down your SQL instance, and other dangerous things.
Until next time, happy upgrading and administrating GP!
Rob