Keyword or statement option ‘default_database’ is not supported in this version of SQL Server.

Product:

Microsoft Azure SQL

Issue:

Can not change default database for a SQL user in Azure SQL. User can not login to database, if it is not possible to select database in the login dialog ( like SSMS). You get this error when you try to change the default database for a SQL Azure user. If you create a SQL user that only resize in a single database, he can only login to that database. With Azure Data Studio can you select the database name during login dialog.

Solution:

Add the user to the master database too, with command:

create user pbi_reader for login pbi_reader

Before that, you may have run below command to created the login:

create login pbi_reader with password = 'Password!'

Switch to the user database, enter below code to create a user there:

create user pbi_reader for login pbi_reader

grant select on schema::DM to pbi_reader

Above command will give the SQL user pbi_reader access to the tables that have schema DM.

Azure SQL does not support the DEFAULT_DATABASE, DEFAULT_LANGUAGE, CHECK_EXPIRATION, CHECK_POLICY commands.

 

If you are db_owner in the SQL Azure database, you can add a Azure AD account to have access, by using a command like this:

CREATE USER [donald.duck@domain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = DM;

To add a user as db_datareader to a specific database, use this command:

EXEC sp_addrolemember 'db_datareader', 'pbi_reader'

To list users in database, use this command:

SELECT * FROM SYS.DATABASE_PRINCIPALS

To be able to connect from Power BI to Azure SQL, you need to open the firewall in the database.

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

https://hevodata.com/learn/azure-to-power-bi/

Power BI in the cloud, uses a span of ip address that change every week, if your database in the cloud or on-prem need to whitelist ip addresses in the firewall to gain access, it is best to eiter try to use a URL/DOMAIN in the firewall to allow PowerBI in the cloud to have access. You can create a support ticket with Microsoft Azure team and get a list of IP addresses that is used by Power BI.

For a on-prem database, the best solution is to install Azure gateway on-prem, then this box will keep the connection between on-prem and the azure based power bi service. And your on-prem databases will talk to the on-prem azure gateway.

The on-premises data gateway acts as a bridge. It provides quick and secure data transfer between on-premises data, which is data that isn’t in the cloud, and several Microsoft cloud services.

For Azure SQL in the cloud, it can be simpler to allow all cloud based services access, by inside https://portal.azure.com go to your SQL server page, then to network, and mark Allow Azure services and resources to access this workspace to be enabled. This could make it possible for Power BI in the cloud to access your Azure SQL database.

You may also be able to set Azure SQL firewall with command like this (in SSMS):

 exec sp_set_firewall_rule N'Allow DB Connections', '10.0.0.2', '10.0.0.2';

 

More information:

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/ 

https://www.microsoft.com/en-us/download/details.aspx?id=56519 

https://learn.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls 

https://radacad.com/the-power-bi-gateway-all-you-need-to-know

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

https://chartio.com/learn/databases/grant-sql-server-table-permissions/

https://www.guru99.com/sql-server-create-user.html

Azure SQL Limitations compared with a SQL Server Enterprise

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-database?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16&tabs=redhat-install%2Credhat-uninstall#install-azure-data-studio

https://devblogs.microsoft.com/premier-developer/secure-access-to-azure-sql-servers-for-power-bi/