How are Microsoft Excel\Office remote clients able to SQL connect to Azure SQL DB, if GRANT CONNECT SQL TO [Login_Name] is not supported

Question

Hello there

When the following script attempts to provide additional SQL user logins, required for Microsoft Office use, the script is not able to execute, and fails with the error message

Securable class 'server' not supported in the server scope in this version of SQL Server.

Script:
USE master

GO

CREATE LOGIN LoginName WITH password = 'password'

GRANT CONNECT SQL TO LoginName

GO

A Microsoft Access or Excel user, from a remote location, attempts to click through the Data linking process via the Azure Database or SQL database buttons. They are presented with the option to connect using Windows Authentication, which Azure SQL does not support, and SQL Connection, which one cannot permit if Grant Connect SQL is disabled or not supported in one's Azure SQL subscription.

The default admin SQL login account has been granted SQL connect permissions, otherwise I would not even be able to do anything with SQL Server Management Studio with the given resource.

How can one efficiently connect, from a remote external location, to shared data, using Microsoft Office, to an Azure SQL database, without using the default admin login parameters?

Answer 1

Chris Tate Thank you for reaching out.

This error message indicates that the GRANT CONNECT SQL statement is not supported in your version of SQL Server.

I was able to successfully run the above query.

To efficiently connect from a remote external location to shared data using Microsoft Office to an Azure SQL database, you can use Azure Active Directory authentication. This allows you to use your Azure AD credentials to authenticate to the database, instead of using a SQL Server login.

To use Azure AD authentication, you need to create an Azure AD user or group and add it to the database. Then, you can use the Azure AD user or group to connect to the database from Microsoft Office.

Here are the high-level steps to use Azure AD authentication.

1. Create an Azure AD user or group and add it to the database.
2. Create a connection string that uses Azure AD authentication.
3. Use the connection string to connect to the database from Microsoft Office.

Here is an example.

Data Source=myserver.database.windows.net;Initial Catalog=mydatabase;Authentication=Active Directory Integrated;

• Open Microsoft Excel or Access and click on the "Data" tab.
• Click on "From Database" and select "From SQL Server".
• In the "Data Connection Wizard", enter the server name and database name that you want to connect to.
• Select "Use the following User Name and Password" and enter the Azure AD user or group that you created earlier.
• Click on "Test Connection" to test the connection.
• Click on "Finish" to create the connection.

Please note that you need to have the appropriate permissions to create an Azure AD user or group and add it to the database. Also, you need to have the appropriate permissions to create a firewall rule to allow external access to the database.

I hope this helps! Let me know if you have any other questions.

Regards,

Oury

Answer 2

In Azure SQL database, create the user as shown below:

1. Run the command in master DB:

    
CREATE LOGIN [YourLoginName] WITH PASSWORD = 'Password1'

2. Go to the user database, run the command bellow:

CREATE USER YourLoginName FOR LOGIN YourLoginName;
ALTER ROLE db_datareader ADD MEMBER YourLoginName ;    
GO 

Please use the Azure SQL server admin to create the login and user above.

Once you create the user as explained above, the user has permission to connect to the database.

Make sure you whitelist the IP address of the user on Azure SQL also.

Finally, follow this tutorial to connect to Azure from Excel using the user you just created,

Answer 3

You need not only to specify the server name when connecting to the Azure SQL database.

But you need to specify the database name.