Unable to fetch User Permissions from AzureSQL

[Ayanmullick]

Before submitting a bug report:

• Ensure you are able to reproduce it on the latest released version (we release often)
• Verified this bug is not already reported in an issue
• Verified errors are not related to permissions
• Can reproduce in a clean PowerShell session (clean = powershell -NoProfile)

Steps to Reproduce

Get-DbaUserPermission -SqlInstance 'tcp:<AzSQL ServerName>.database.windows.net,1433' -Database <AzSQLdbName> -SqlCredential <SQLadmin> -Verbose|ft

Expected Behavior

ComputerName	InstanceName	SqlInstance	Object	Type	Member	RoleSecurableClass
ServerName	MSSQLSERVER	ServerName	SERVER	SERVER LOGINS	CORP<Group>	None
ServerName	MSSQLSERVER	ServerName	SERVER	SERVER LOGINS	CORP<Group>	None
ServerName	MSSQLSERVER	ServerName	SERVER	SERVER LOGINS	CORP<Group>	None

Actual Behavior

VERBOSE: [19:13:53][Get-DbaUserPermission] Processing [<>] on TCP:<>.database.windows.net,1433
VERBOSE: [19:13:53][Get-DbaUserPermission] Creating objects
VERBOSE: [19:13:53][Get-DbaUserPermission] Building data table for server objects
VERBOSE: [19:13:53][Get-DbaUserPermission] Building data table for [<>] objects
VERBOSE: [19:13:53][Get-DbaUserPermission] Deleting objects

Environmental data

• PowerShell:

Name	Value
PSVersion	5.1.14393.2636
PSEdition	Desktop
PSCompatibleVersions	{1.0, 2.0, 3.0, 4.0...}
BuildVersion	10.0.14393.2636
CLRVersion	4.0.30319.42000
WSManStackVersion	3.0
PSRemotingProtocolVersion	2.3
SerializationVersion	1.1.0.1

• SQL Server: Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 19 2018 08:43:17 Copyright (C) 2018 Microsoft Corporation

[EmanueleMeazzo]

Maybe it's the same issue that I have with another cmdlet #5122 , because the way which DBATools connects to the instance (connecting to the default DB and then switching to the -Database) doesn't work with Azure SQL DB

[wsmelton]

Be aware that we do not actively support Azure SQL in every command. Commands that we have using strictly SMO are not (on average) going to all work against Azure SQL because SMO is not supported for it. Azure SQL is commonly managed by Az module, Azure CLI and the REST API.

This command is going to be an example @Ayanmullick where it will never work on Azure SQL because we are querying DMVs that are not allowed in Azure SQL. To gather permissions for a given user we are querying the syslogin view, that view is not accessible in Azure SQL. We also explicitly call out use for creating tables in tempdb, which I do not believe works in Azure SQL either but have not tried.

[Ayanmullick]

@wsmelton I verified with our MS TAM. SMO is MS-supported on AzSQLdb.

[wsmelton]

On this command it is irrelevant as this command as-is will not support use against Azure SQL.

[Ayanmullick]

The below will connect you to an AzSQLdb thru SMO

Import-Module sql-smo
$sqlcc = new-object ('System.Data.SqlClient.SqlConnection') "Server=tcp:<>.database.windows.net,1433;Initial Catalog=<>;Persist Security Info=False;User ID=<>;Password=<>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';"
$sc = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc

$srv.Databases|ft #validate connection

[wsmelton]

We don't provide plain text passwords on the module... Not allowed and PS gallery will flag it if we do.

To support that it requires use of pscredential object, not providing a custom connection string (unless it can be done without passing a plain text passwords). The main internal connect command will have to be modified if SMO indeed supports it.

[potatoqualitee]

we were planning on support for azure integrated auth, it seems like fun, i'll see what i can do tonight.

#4670

[wsmelton]

@potatoqualitee be aware that fixing the auth issue will not fix this command for Azure SQL. As stated above this command, Get-DbaUserPermission, uses code that is not supported for Azure SQL. The STIG queries are not written to be run against Azure SQL databases.

[potatoqualitee]

Thank you @wsmelton. It is true, the underlying T-SQL code, as provided by DISA, does not support this type of permission export. We can now authenticate to Azure SQL Db using various auths, and SMO does work (albeit in a limited capacity, by design) but DISA's T-SQL does not support Azure. If that changes, we'll add it to this command.