Query
I need to test connectivity and retrieve values from database on Azure SQL server.
Solution
Create two files, as below and run
- TSQL query file
- Powershell
I saved below Scriptfile ona local path '..\Select.sql'
1
2
3
4
5
6
7
8
DECLARE @SqlCmd VARCHAR(4096);
BEGIN
SET @SqlCmd = 'SELECT TOP(2) * FROM [dbo].[Employee]';
PRINT @SQlCmd
EXEC (@SQlCmd)
END
and powershell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
#region Connect to Azure SQL DB using Service Principal Account
$TenantId = "XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX"
$ServicePrincipalApplicationId = "XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX"
$SqlServerName = "<Sql Server Name>"
$DatabaseName = '<DatabaseName>'
$clientsecret= "<secret in plain text>"
$ScriptFile = '..\Select.sql'
# Connect to Azure
Connect-AzAccount -ServicePrincipal -Credential $Credential -TenantId $TenantId | Out-Null
# Generate the Token
$body = @{
scope = "https://database.windows.net//.default"
client_id = $ServicePrincipalApplicationId
grant_type = "client_credentials"
client_secret = $clientsecret
}
$params = @{
ContentType = "application/x-www-form-urlencoded"
Headers = @{ accept = "application/json" }
Body = $body
Method = "POST"
URI = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
}
$token = Invoke-RestMethod @params
$sqlAccessToken = $token.access_token
#$token
$SqlServerName = $SqlServerName.ToLower()
$DatabaseName = $DatabaseName.ToLower()
Write-Host "Connecting to $DatabaseName"
$query = [IO.File]::ReadAllText($ScriptFile)
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Data Source=tcp:$SqlServerName.database.windows.net,1433;Initial Catalog = $DatabaseName;Connection Timeout=30";
$Connection.AccessToken = $sqlAccessToken
#$Connection
try {
Write-Host "Opening Connection"
$Connection.Open()
Write-Host "Creating Command"
$Command = $Connection.CreateCommand()
$Command.CommandText = $query
$Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
}
catch [System.Data.SqlClient.SqlException] {
Write-Host "SqlException...Check your Query and Data then try again"
}
catch {
Write-Host "An error occurred"
Write-Error $_
}
finally {
if ($Connection.State -eq "Open") {
Write-Host "Closing Connection"
$Connection.Close()
## print Dataset after closing connection.
$dataSet.Tables
}
} # close finally
Sample Output
Disclaimer: This is a personal weblog. The opinions expressed here represent my own and not those of any entity with which I have been, am now, or will be affiliated.