Issues connecting Azure DB to Excel

Question

Hi

• Excel 2016 Profession Plus
• Azure SQL Managed Instance (PaaS)

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

https://learn.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://learn.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

Answer 1

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.

Answer 2

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?

Answer 3

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

Answer 4

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://learn.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

Answer 5

Try using the following:

Then follow the steps: