Cannot use Azure SQL with Azure DevOps Server 2019 - (Azure Government)

3
votes

enter image description hereenter image description hereI have been struggling to get my DevOpsServer 2019-RC1 installation to see my Azure SQL Server

My DevOpsServer install is on an Azure VM, as recommended

I have implemented everything from the below documentation regarding the set up of an MSI for the VM, and granting the MSI access to Azure SQL via AAD authentication:

I have also added the VNET where the VM NIC is located, as a VNET firewall rule on the Azure SQL Server to ensure there are no connection issues

My DevOpsServer refuses to see the Azure SQL Server or its databases

To confirm that Azure SQL is not blocked from the VM, I successfully created an ODBC system DSN connection on the VM, which allows me to see the Azure SQL Server, and its Databases

Per the reference documentation:

  • When setting up a new DevOps Server instance, i selected "This is a new Azure DevOps Server deployment"
  • On the Database page of the configuration wizard, specify the Azure SQL Database server instance, typically in the form of "SQLInstanceName.database.windows.net"

Please let me know if there is anything else i can do to help the Devops Server Configuration Wizard see my Azure SQL Server and Databases

PS I am trying to get this working in Azure Government (MAG) if this changes the capability...

Error received when attempting to connect to the SQL Database programmatically via the following Powershell script:

# Retrieve the access token
$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.usgovcloudapi.net' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token

# Create the connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source = test-sqlsrv.database.usgovcloudapi.net; Initial Catalog = inldb"
$SqlConnection.AccessToken = $AccessToken
$SqlConnection.Open()

# Send a Query
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT * from <TABLE>;"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

enter image description here

4
azure-devopsvirtual-machineazure-sql-serverazure-gov
I realize this is very basic and you've probably already checked this - but just to be safe: did you make sure the use the Azure Gov connection string? "SQLInstanceName.database. usgovcloudapi .net" (not "SQLInstanceName.database.windows.net") – Steve Michelotti
Thanks for the reply. Yes i am copying the SQL Server Name straight out of the Portal for accuracy. Seems odd that it will not see Azure SQL with the proper firewall rules in place, and the VM having its MSI as a SQL Srv/ database user – Gvazzana
When i enter the Azure SQL Server in the configuration Wizard and hit "test" sometimes i can get it to give the error: "TF255518: Failed to authenticate with the database. Check that the user has access". I am starting to wonder if it is trying to use the local VM user account i am logged into, rather then the VM MSI which has access to the Azure SQL Database. This VM is not on a domain... so is that an issue, that it cannot use the AAD Credentials? – Gvazzana

4 Answers

2
votes

Try running ALTER USER [VMName] WITH DEFAULT_SCHEMA=dbo from both the collection and config DBs. I have a PR out to add this to the documentation and this might be causing your issue.

0
votes

Try connecting to the SQL instance via Powershell instead of Azure DevOps Server.

You can run the following script to do that:

# Retrieve the access token
$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token

# Create the connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source = <AZURE-SQL-SERVERNAME>; Initial Catalog = <DATABASE>"
$SqlConnection.AccessToken = $AccessToken
$SqlConnection.Open()

# Send a Query
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT * from <TABLE>;"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

This should at least give you a more descriptive error to debug.

0
votes

Gvazzana, I also recommend testing with the script danwkennedy provided, but be sure to change database.windows.net to database.usgovcloudapi.net for Azure Government.

I would pay close attention to any errors around obtaining an access token and errors associated with opening the SQL connection.

If nothing's standing out is it possible to capture a Fiddler trace?

0
votes

I agree that you should perform the PowerShell test to test your MSI. It looks like you are having issues reaching the IP to get the access token. If you are attempting to connect from a Government network, it is likely that you are going through some type of web proxy service (Blue Coat, etc.) to get to the internet. You can maybe trick the proxy by emulating the user agent string from a browser by using the -UserAgent parameter on your Invoke-WebRequest command.

If that doesn't work, you may need to reach out to your proxy team to troubleshoot the issue.

Lastly, if you are able to test successfully in PowerShell, but you still get the connection test error in the DevOps install, make sure to follow the steps here:

https://docs.microsoft.com/en-us/tfs/server/install/install-azure-sql

I forgot to add the user to the master DB... Once I did that, everything worked. Good luck.