Invoke-D365SqlScript Broken in Platform 30+?

[MeritJB]

We are trying to get some SQL automation around refreshes, and are having issues executing this against MS Azure DBs for Tier 2. It seems that not having DB access to the Master DB of the server and only the AXDB itself could be blocking something ?

[Splaxi]

Could you share a bit more details or even a sample script that you're executing? I know that some environments are becoming Self-Service, which are changing things a bit. But there is also running a background migration where the SQL DB are being migrated the spartan servers, which might require a few adjustments in the module.

I'm keen to assisting you, but it might take a few tries to get it right, because I don't have access to the same kind of environments as you.

[Splaxi]

And please share your overall goal and process, to see if it fits into how the module works.

I might some ideas for workarounds and small hacks, based on what you're trying to solve.

[MeritJB]

Thanks! We are looking to execute a simple post refresh SQL script to change and redact some environment information after a refresh takes place. We believed that the .sql file would be easy enough to execute against a MS owned T2 Azure DB. Everything works fine when it is run interactively via SSMS, we want to automate that. The PS module here seemed to fit that bill.

I think the issue is that the PS command is trying to access the Master DB of the server, but is being blocked by the FW rules, since the spartan servers only grant FW access on the DB level, not the server level.

Let me know your thoughts. Incidentally I'm also struggling with the token access and Set-D365LcsApiConfig as well.

[Splaxi]

I can understand that think the issue is something with connecting to the master database, when it seems that things are not working.

Because we for now are "only" talking about the spartan database, I was able to find a customer environment to conduct some testing against. So everything from here on will only work on a NON-SelfService environment, where you are connected to the VM for your Tier2+ environment via RDP. SelfService is another story.

I created a local test.sql file and saved it to c:\temp\d365fo.tools\test.sql. The content of the file is:

CREATE TABLE [ax].[_temp](
    [Id] [int] NULL
)

The code will simply create a new table, the very first in the list of tables when looking for it from inside SSMS.

At first I was worried that the module didn't load the connection details correctly from the files that are stored on the VM. So I went to fetch the latest details directly from the LCS page for the specific environment and ran the following command:

Invoke-D365SqlScript -DatabaseServer "spartan-srv-emea-d365opsprod-123456789.database.windows.net" -DatabaseName "db_d365opsprod_20200205_07470351_1111" -SqlUser axdbadmin -SqlPwd "123456789" -FilePath C:\Temp\d365fo.tools\Test.sql

Afterwards I went into SSMS and made sure that the table was created:

I started to look into the code and made sure that the code would actually load the correct details for creating the connecting to the spartan database, so I ran the command just plain and simple, like it was meant to be run:

Invoke-D365SqlScript -FilePath C:\Temp\d365fo.tools\Test.sql

and the result is the same:

So - could you please try and replicate this small test case on your environment. Please start with the command where you are using all the details from the environment page inside LCS, to see if that works.

You could also run the following command and compare the details from that with the details available on the environment page on LCS:

Get-D365DatabaseAccess

Please start separate issues the for Lcs and token stuff, so we can keep things apart. It is easier to share a thread with a specific issue with some of the peers from the community if we end up in a place where I can't figure out what is wrong.

As stated earlier, it would be nice if you could share the command that you're executing and a little more context on how you do it, to see if I missed anything obvious in my understanding of what you're doing. You should naturally sanitize your example so you don't share any secrets / credentials, but it could still prove helpful for me helping you out.

[MeritJB]

I'm a little confused/concerned about the self-service distinction. We are talking about self-service implementation projects here, as opposed to partner projects. This is where the Tier 2 environments with Azure SQL backends are coming from. I still have some testing to do in T1 environments with local editions of SQL resident.

I believe the key here is the Get-D365DatabaseAccess... This essentially returns null configs: ` Database : ReadOnlySecondaryDbServers : {} SqlPwd : SqlUser : AxAdminSqlPwd : AxAdminSqlUser : DbServer : IsManagedDataAccess : False DefaultDbDataReaderType : 0 DataReaderBufferSize : 0 UseLegacySqlConnectionState : False UseManagedStackForTilesQuery : True DataEncryptionCertificateThumbprint : DataSigningCertificateThumbprint : DataEncryptionCertificateThumbprintLegacy : DataSigningCertificateThumbprintLegacy : UseManagedStackForReportsQuery : False EnableXDSForManagedStack : False EnableStatementCache : False EnableDataCache : False

ConfigurationTransformation : `

I was investigating the tool set for commands that would populate or set this data, much like Get-D365LcsApiConfig & Set-D365LcsApiConfig, but didn't find anything.

Just for context, the command I'm running is below, much like yours Invoke-D365SqlScript -FilePath "C:\PostRefresh.sql" -DatabaseServer spartan-srv-nam-d365opsprod-xxxxxxxxx.database.windows.net -DatabaseName db_d365opsprod_xxxxxxxxx_6d72 -SqlUser axdbadmin -SqlPwd 2xxxxxxxxx=

This comes back with a firewall IP error that I first noted: [11:44:01][Invoke-D365SqlScript] Something went wrong while executing custom sql script against the database. | Exception calling "Open" with "0" argument(s): "Cannot open server 'spartan-srv-nam-d365opsprod-xxxxxxx' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the serve r. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect." WARNING: [11:44:02][Invoke-D365SqlScript] Stopping because of errors. | Something went wrong while executing custom sql script against the database.

I can confirm that I have my IPs against the DB correct, because I can connect with the same information via SSMS and execute the query, but also check the FW to ensure my IPs are valid. select * from sys.database_firewall_rules

Happy to share more info if it would help.

[Splaxi]

Let me start by asking in another way:

Are you able to RDP into the AOS servers (VM's), with details coming from the LCS page? Or are you running the module / tools from a machine that doesn't have any D365FO components installed?

[Splaxi]

Just for the record. I followed the following guide and opened a firewall rule for my private IP address at home.

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-debugdiag#add-your-ip-address-to-a-whitelist

After confirming access via SSMS, I simply created the same test.sql file on machine, and ran the exact same command from earlier, and everything still checks out and the result is the that the table is created as expected.

So - I think we either need to take over to some screen sharing / more interactively session, where we can dig into the issues together.

To sum up my testing: If the firewall rules are in place and the details from LCS are being used, the Invoke-D365SqlScript cmdlet / function is capable of connecting to any Tier2 database, be that old fashion Azure SQL DB, or the new scale out, spartan servers.

[MeritJB]

Are you able to RDP into the AOS servers (VM's), with details coming from the LCS page? Definitely

I think we either need to take over to some screen sharing / more interactively session I'm game for that, please reach out via email and I can coordinate some sessions on Monday if your schedule allows.

I wanted to try something since you mentioned AOS servers... I have been using the BI server in the environment group, because that's typically "left alone" in T2 environments. I tried the exercise from the AOS server specifically, and the Get-D365DatabaseAccess command returned VALID data. The next attempt of my script there worked.

So we have a variant between us where I seem to "need" the AOS, and you can use any external server.

[Splaxi]

Ah! Yeah - the BI server isn't fully deployed with all the components we need to be able to extract the connection details for a environment. That is a limitation we never stated anywhere clear - you need to run this on an AOS VM, if you want to benefit from all the magic preloading of connection details to the database.

When I'm saying that I'm able to work from an external server / machine, I still manually fill in all the details like servername, databasename, username and password. My point was that there wasn't any technological limitations in place, that broke our tools / module, when running it from any server in the world. As long as there firewall rule is in place, and you fill in all the details like my sample script shared earlier - then it should work.

I'm guessing that you are located in the US, based on the time you are able to post in the thread? I'm located in the CET (UTC+1) timezone, but if you feel like you need a hand with this Sql script let me know and I'll see what I can do.

If you had other stuff that didn't work, please create a new issue with all the details you cand and I'll see if I can guide you or we should take it over a Skype / Teams call during next week.

[MeritJB]

After some more investigation and testing with various servers, I think we are OK for now. I've got a better understanding of how these are working. I will re-open and re-engage if things start to slip again. Thank you for all your help and guidance!