Logging in to SQL Server in the developer VM

Connecting to SQL Server in the Dynamics 365 for Operations development VM

This post assumes you are comfortable in the use of SQL Server. If you are not, this is a good entry to go ahead and skip.

EDIT: This post might not be necessary. I am told that due to UAC, all you need to do is launch SSMS using “run as administrator” instead. I’ll leave what I wrote here for historical purposes (it’s still interesting) but you don’t need it!

Coming from a SQL Server background, I have frequently found it helpful to dig through the tables in AxDB, the SQL Server database used as a datastore for Dynamics AX / Dynamics 365 for Operations. Although we do not have direct access to them when programming, and the “Tables” in the AOS are an abstraction that sometimes differs, they are often analogous; it can be easier to plan out a join in familiar T-SQL before trying it in X++. It’s also a good troubleshooting and debugging tool.

And, let’s face it… Microsoft might not like it, but once in a blue moon it can be easier to do certain cleanup or bulk updates that way, if you know what you are doing. Not the best practice, but certainly a fast way to clear a new table you are building, or activate a hundred imported users in one shot…

Working on platform update 3 and earlier, all you had to do was fire up SSMS (SQL Server Management Studio) while you were logged in as the VM Administrator, and (per defaults for most SQL Server installations) you were a sysadmin. After moving to a fresh Platform Update 5 VM (skipping PU4), however, I found that they’d configured SQL Server to no longer allow you access. Not cool, Microsoft.

However, there is a workaround. As you might know, there is a web.config file that contains information Dynamics 365 for Operations uses to connect to its datastore, including a sysadmin user and password. Although the password is stored “encrypted,” you can decrypt it temporarily. Just do that and find the relevant entries in the web.config file:

Although “security through obscurity” ain’t great, I’m not going to put the actual platform update 5 default password here, especially since it might change anyway.

If you want long-term convenience, you might want to immediately log in with these credentials and re-add the local administrator as a login with the sysadmin role. (If you don’t know how to do that, maybe you shouldn’t be mucking around in the SQL Server after all!) Then you don’t need to remember or save the password you dug out of here.

Oh, and… don’t forget to re-encrypt the web.config file when you’re done.

ADDENDUM: In case something happens to the linked post, here are the commands to decrypt and re-encrypt the web.config file. But please, go through and comment on the original post… and comment here too… it motivates bloggers to keep going when we get the occasional response. Just a quick “thank you” is plenty!

C:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Framework.ConfigEncryptor.exe -decrypt C:\AOSService\webroot\web.config

C:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Framework.ConfigEncryptor.exe -encrypt C:\AOSService\webroot\web.config

6 thoughts on “Logging in to SQL Server in the developer VM”

  1. What can I say Mr. Brian!! I think I am in love with you lol..I was digging into the same folders for the same objective..I dont know how did I overlook the encryptor..


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: