Can SQL Profiler connect to cloud DBs like Azure Synapse? Thanks!
SQL Data Profiler has been tested with Azure SQL Database instance using the DNS connection string (xxx.database.windows.net) and a SQL Login. This requires you to grant firewall access on Azure to the IP Address where SQL Data Profiler is installed. It has not been tested with Azure Synapse which also supports DNS addresses as well, so theoretically it should work. See Connect to Synapse SQL with Azure Data Studio - Azure Synapse Analytics | Microsoft Docs for information about using connection properties.
Please note, that the Connection dialog in SQL Data Profile does not currently support Azure Active Directory accounts.
I was able to log in to the Synapse space with a custom SQL login and select a DB, but was immediately informed that I needed View Server State permissions. Unfortunately, granting this permission is not possible
For an explanation of why this is so, see KB: Required SQL Server permissions - SQL Data Profiler - Free Tools - IDERA Community. It is possible that Azure Synapse does not have the requisite system view that SQL Data Profiler needs to collect metadata.
Is it a fair assumption then that SQL Data Profiler is not able to connect to Azure Synapse as there is no "View Server State"...is there an alternate way to possibly get around this?
SQL Data Profiles makes use of two INFORMATION_SCHEMA views - INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES. Based on this link (System views for dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics | Microsoft Docs), these System views appear to be available for a dedicated SQL pool in Azure Synapse Analytics. As noted in this link (System Information Schema Views (Transact-SQL) - SQL Server | Microsoft Docs), "The visibility of the metadata in information schema views is limited to securables that a user either owns or on which the user has been granted some permission". This permissions requirement was written for SQL Server 2019, so it is unclear whether it differs for Azure Synapse or not.
Use these links for additional guidance:
GRANT-DENY-REVOKE permissions - Azure Synapse Analytics | Microsoft Docs
How to set up access control for your Synapse workspace - Azure Synapse Analytics | Microsoft Docs
Once you think you have the right permissions granted to the user of SQL Data Profiler, you can test by running one of two queries:
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
or re-running SQL Data Profiler and see if the data appears.
Powered by IDERA