Update Azure SQL Database table using Service Principal Context

The script below checks whether a key exists in the DB and if yes it updates and if not it adds the key and value to the table. The context used here is the Service Principal Name (SPN) which is the client ID and secret key. Also the SQL authentication user ID and password is required. Make sure your machine ip is added in the firewall rules to run the query.

$dbuser = "username"

$password = "dbpswd"

$tenid = "tenantid"

$clientid = "client ID"

$secretkey = "Secret Key"

$Servername ="dbservername"

$database="dbname"

Write-Output "Starting"

#$clientid = Get-AzureRMAutomationVariable -Name $varclientid

#$secretkey = Get-AzureRMAutomationVariable -Name $varsecretkey

#$dbuser = Get-AzureRMAutomationVariable -Name $vardbuser

#$password = Get-AzureRMAutomationVariable -Name $vardbpass

#$sbpk = "test"

Add-SqlAzureAuthenticationContext -ClientID $clientid -Secret $secretkey -Tenant $tenid

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = "Server=$Servername.database.windows.net; User ID = $dbuser ; Password = $password ; Database = $database; Column Encryption Setting=enabled;"

$sqlConn.Open()

Write-Output "sql conn opened"

function updateparamMaster($sqlconn,$paraID,$ParaGrp,$ParaValue)

{

#Check if the paramid and ParamGroup exists

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$query = "select * from parametermaster where paramid= $paraID and ParamGroup= '"+$ParaGrp+"'"

$sqlcmd.CommandText = $query

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

$data = New-Object System.Data.DataSet

$adp.Fill($data)

$paramcount = $data.Tables[0].Rows.count

Write-Host "Row count-" $paramcount

if($paramcount -eq 0)

{

Write-Output $ParaGrp "ParamGroup does not exist- creating new entry"

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "INSERT into parametermaster(paramid,ParamGroup,Value) VALUES (@paramid, @ParamGroup, @Val)"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@paramid",[Data.SQLDBType]::Int)))

$sqlcmd.Parameters["@paramid"].Value = $paraID

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ParamGroup",[Data.SQLDBType]::VarChar, 50)))

$sqlcmd.Parameters["@ParamGroup"].Value = $ParaGrp

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@val",[Data.SQLDBType]::NVarChar, 500)))

$sqlcmd.Parameters["@val"].Value =$ParaValue

$sqlcmd.ExecuteNonQuery();

}

else

{

Write-Output $ParaGrp "ParamGroup exist- updating entry"

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "UPDATE parametermaster SET [Value] = @Val WHERE paramid = @paramid AND ParamGroup = @ParamGroup"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@paramid",[Data.SQLDBType]::Int)))

$sqlcmd.Parameters["@paramid"].Value = $paraID

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ParamGroup",[Data.SQLDBType]::VarChar, 50)))

$sqlcmd.Parameters["@ParamGroup"].Value = $ParaGrp

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@val",[Data.SQLDBType]::NVarChar, 500)))

$sqlcmd.Parameters["@val"].Value =$ParaValue

$sqlcmd.ExecuteNonQuery();

}

}

updateparamMaster -sqlconn $sqlConn -paraID 118 -ParaGrp "SharedDb" -ParaValue