How to create user in Azure Sql Database using Invoke-Sqlcmd (powershell)?

user_11789925 0
Viewed 649

I am trying to create users in Azure SQL Database using PowerShell. I have stored my sql file in local.

I have tried using Invoke-Sqlcmd command:

$adminLogin = example@domain.com
$createUserSQLFile = "C:\filepath\createUser.sql"

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -U $adminLogin -InputFile $createUserSQLFile

But I always get an error

Cannot open server "domain.com" requested by the login

I have already set up $adminLogin user as Active Directory admin. What am I missing here?

azure-functions azure-sql-database powershell
1 Answers

You need to specify you will be using Active Directory authentication.

$Creds = Get-Credential -Credential 'username@domainname.onmicrosoft.com'
$Username = $($Creds.GetNetworkCredential().UserName)
$Password = $($Creds.GetNetworkCredential().Password)
$Database = "testg"
$Server = 'test.database.windows.net'
$Port = 1433
$cxnString = "Server=tcp:$Server,$Port;Database=$Database;Authentication=Active Directory Password;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
$query = "CREATE USER YourUser WITH PASSWORD = 'JRxuerCc(q'"
$cxn = New-Object System.Data.SqlClient.SqlConnection($cxnString)
$cxn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($query, $cxn)
$cmd.CommandTimeout = 120
$cmd.ExecuteNonQuery()
$cxn.Close()

Make sure you also added your public IP address to Azure SQL Database firewall rules.

user_81841390
Related