Finance | Project Operations, Human Resources, ...

Firewall Access to Elastic Pool db

(0) Share

Report

Posted on by Donal

270

Since F&O split the databases out into elastic pool i am no longer able to connect to the master database, in this instance it is the AxDW server.

I am trying to run the Azure firewall rule for remote access to an Azure database.

Script attempted to run;

USE Master

exec sp_set_database_firewall_rule N'MURSITAXDW_MM', '212.145.67.190', '212.245.256.256';

When I attempt to connect a box appears - New Firewall Rule - it asled for my Azure login details but then errors with message under the Details hyperlink of;

Can anyone help please ?

Cheers

Donal

I have the same question (0)

All responses (12)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You need to specify the database name when you try to connect. You are indeed not allowed to connect to the master database anymore. If you want to connect to AxDW, you need to specify the AxDW database name (which you find in LCS).

Was this reply helpful? Yes No
Donal 270 on at

Like (0)

Report

Does that mean that a manual export from a Sandbox envt using the CREATE COPY query and "SqlPackage.exe /a:export" CMDlet are no longer possible?

All T2 to T1 refreshes have to be performed using the LCS self-service function?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

I haven't used the command line stuff for export. I don't know why it would not work just because you don't have access to the master database. But yes, might be true that you can't anymore create new databases in the Sandbox. instead you would have to export directly from the AxDB.

But why not use the LCS export tool? You can call that automatically via API nowadays.

Was this reply helpful? Yes No
Suggested answer

Adria Ariste User Group Leader on at

Like (0)

Report

Have you tried connecting to the FnO AxDB DB (even though it has another name) as Nikolaos suggests and add the firewall rule there? I had this same issue and I think that worked for me.

Adrià Ariste - Senior technical consultant @ Axazure - Read my blog!

Was this reply helpful? Yes No
Jan M. 3,262 on at

Like (0)

Report

Hi Donal

As you don't have the SQLAdmin user anymore, you are not able to run this command. "Create copy" command, what works only for Azure DB, needs also master db access. You cannot add the firewall rule if this server belongs to Microsoft Azure subscription.

Additional info by MS LCS release notes cloudblogs.microsoft.com/.../

Now for AxDB we exchange data by the Asset Library with many disadvantages:

a) During export the database will make this environment temporarily unavailable, strange...

b) Import is not possible in a second DB, means DB is blocked during hole Import

b) Export / Import cannot be automated with scripts

> export .pacpac is possible but leads in error wenn importing because of wrong user/group from Azure (prepare not possible)

c) Export / Import is much slower than with direct commands, as LCS does more steps e.g. for rolleback

d) Upload / download time while moving the .pacpac file

Regards

Jan

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

I think the disadvantage list is not completely accurate.

The source db is not unavailable during export. Yes, the dialog might say so but it's not true. Or did you experience downtime? You can also refresh db between Tier2+ environments with one LCS task.

I also believe that the ADO/LCS based automation tasks for database management are coming available in the near future

Was this reply helpful? Yes No
Suggested answer

Adria Ariste User Group Leader on at

Like (0)

Report

I confirm this works. Log into to the AOS VM, there connect to the DW db using SSMS and execute the firewall command on the DW DB. You'll be able to connect to the DB then from the IP you've opened the firewall for.

And I guess that you can use the SqlPackage tool too.

Adrià Ariste - Senior technical consultant @ Axazure - Read my blog!

Was this reply helpful? Yes No
Jan M. 3,262 on at

Like (0)

Report

Thanks Adria, have you used the SQLAdmin user for running the sp_ command to set the firewall rule?

The problem is that "create copy" needs master access, so you are not able to prepare the db for export to 1-tier.

Was this reply helpful? Yes No
Adria Ariste User Group Leader on at

Like (0)

Report

To connect to the DW DB I've used the axdwadmin user, to do the same in the "AxDB" I used the axdbadmin user but I got the same results with the axdeployuser.

Yes, you're right, I didn't think of the part where we need to copy the DB so the SqlPackage tool is a bit useless then, unless it's possible to clean up users and so after exporting the DB.

Adrià Ariste - Senior technical consultant @ Axazure - Read my blog!

Was this reply helpful? Yes No
Jan M. 3,262 on at

Like (0)

Report

Negativ to set firewall rule with sp_set_firewall_rule. Tried with axdbadmin or axdeployuser with the result: User must be in the master database.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Firewall Access to Elastic Pool db

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Firewall Access to Elastic Pool db

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates