question

Clodola avatar image
0 Votes"
Clodola asked Clodola commented

Issues connecting Azure DB to Excel

Hi

  • Excel 2016 Profession Plus

  • Azure SQL Managed Instance (PaaS)

I have gone via this tutorial but I am getting an error

https://docs.microsoft.com/en-ie/azure/azure-sql/database/connect-excel

Error
Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"

**I can connect to the DB in SSMS

Need some pointers

I have come across a few possible area's but they are very generic , no idea if I am going down the wrong rabbit hole

  • IP Firewall Rules required?

  • The SQL Azure Database service is only available with TCP port 1433 ? If true?

  • https://docs.microsoft.com/en-gb/azure/azure-sql/database/firewall-configure


  • it would require an Office 365 subscription managed through Azure Active Directory and the Azure SQL Database be managed through the same Azure Active Directory instance. There is no specific Excel Add-In to enable Azure AD authentication -- Not sure what the resolution is here




azure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Clodola Were you able to follow guidelines in the doc shared by Alberto to troubleshoot the issue. Please let us know if you are still facing the same.
Regards,
Oury

0 Votes 0 ·

@Clodola Please try this 108240-image.png and once you land on the page click on create a support request on the right hand side of your screen. Please make sure you select the right subscription associated with support plan.

108263-image.png
Best Regards,
Oury


0 Votes 0 ·
image.png (1.4 KiB)
image.png (38.7 KiB)
Clodola avatar image
0 Votes"
Clodola answered Clodola commented

Thanks Alberto

I tired this before , reason I asked about the firewall rules & TP port

I think we are missing Active Directory -- Universal with MFA Support in Excel , maybe MS removed for security reasons

105811-capture.png



capture.png (18.5 KiB)
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Clodola Could you please try to open a port for the client IP address.?
Regards,
Oury

0 Votes 0 ·

Hi Oury

This is a SQL managed instance , I cannot find a doc specific to opening a port for the client IP address

My access control level is Contributor , under networking , I can see Public endpoint (data) is enabled , Port 3342

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure#:~:text=Port%203342%20is%20used%20for,be%20changed%20at%20this%20point.

I have tried to use this in the Excel connection string , I use the port in the conn strung to connect to SSMS
(I do not need to connect to the VPN)

Conn String
XXX.database.windows.net,3342

When you suggest opening a port for the client IP address, are you referring to Allow public endpoint traffic on the network security group?

mi-nsg-rules.png


0 Votes 0 ·

@Clodola Did you configure connection string properly ? It should be <mi_name>.public.<dns_zone>.database.windows.net,3342 if you are going through public endpoint.
Please let me know
Regards.
Oury

0 Votes 0 ·
Show more comments
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Try using the following:

105340-image.png

Then follow the steps:

105417-image.png



image.png (35.9 KiB)
image.png (22.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Clodola avatar image
0 Votes"
Clodola answered

Thanks Alberto

It does not appear to be an option in excel (2016 Profession Plus) , even though StackOverflow thread has screenshots of the option in Excel 2016

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview#universal-authentication-limitations

SSMS and SqlPackage.exe are the only tools currently enabled for MFA through Active Directory Universal Authentication.

Maybe MS changed tact since the StackOverFlow Thread was created ?

This is what I can only see within excel

105416-capture.png



capture.png (19.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Please follow the steps provided on this StackOverflow thread to connect Excel with Azure SQL using AAD with MFA.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Clodola avatar image
0 Votes"
Clodola answered Clodola edited

Thanks , will run through the guide today ,

SSMS Authentication is under the Active Directory -- Universal with MFA Support , with MFA , if this is a factor?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

If the Excel program is on the same computer as the SSMS program, then you have already created the appropriate Azure SQL firewall entry. TCP port 1433 is the only port Azure SQL do listen, but again with SSMS you get connected.

Please refer to this troubleshooting guide.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.