3

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

Gvazzana
  • 583
  • 1
  • 8
  • 21
  • 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 Dec 20 '18 at 17:55
  • 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 Dec 20 '18 at 18:25
  • 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 Dec 20 '18 at 18:30

5 Answers5

2

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.

Ilya Tillis
  • 21
  • 1
  • I created an AAD group which i added the VM MSI to. That AAD group was then added as a user in both Azure SQL Databases. I have granted the AAD group (User) the "ALTER USER [VMName] WITH DEFAULT_SCHEMA=dbo" as suggested, putting the name of the VM MSI, in the [VMName] section. Should this work? I am not seeing any change since adding this role to the DBs. I also have the VM MSI as the Active Directory Admin, which i thought would give Owner rights to the whole SQL Server – Gvazzana Dec 21 '18 at 19:49
  • Correction, putting the name of the AAD group(User) in the [vmname] section – Gvazzana Dec 21 '18 at 21:25
  • I believe when we check for connectivity we're actually reaching out the the master DB, you'll want to make sure that the AAD group is added a user to that one as well. My reading of the above was you only did that for the collection and config DB. Also I noticed in the documentation that we have `ALTER ROLE [dbmanager] ADD MEMBER [VMName]` for the master db. The brackets on dbmanager may not be needed. It may be worth rerunning that one from master as well, along with `ALTER USER [VMName] WITH DEFAULT_SCHEMA=dbo` as you did for the other DBs. – Ilya Tillis Dec 22 '18 at 15:30
  • Ok I will give that a try. Am I incorrect in my thinking, that if I had the vm msi added to the SQL active directory admin, that it becomes an owner of all DBs in the entire sql server, despite using any additional per DB commands? I would that that would be all the rights it needed to beable to connect – Gvazzana Dec 22 '18 at 17:17
  • I added the vm MSI directly to the master db, tfs_configuration db and tfs_ collection db. Ran "ALTER ROLE [dbmanager] ADD MEMBER [vm MSI]" on master db ( with and with out brachets) , and "ALTER USER [vm MSI] WITH DEFAULT_SCHEMA=dbo" on all 3 dbs. Firewall is disabled on Vm, vnet and vm ip added to Azure SQL Server. DevOps Server Wizard will not let me connect. Instantly receive "TF255518: Failed to authenticate with the database. Check that the user has access"... Shouldnt making the vm MSI the SQL Active Directory Admin, solve all this? – Gvazzana Dec 24 '18 at 16:13
  • Image with error added to original question for reference. This is in the "New Deployment - Azure" selection of the Install Wizard – Gvazzana Dec 24 '18 at 16:30
0

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.

danwkennedy
  • 121
  • 10
0

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?

Bernie Ellis
  • 333
  • 1
  • 5
  • @ Bernie-MSFT and @danwkennedy, i pasted the script and errors received in the post at the bottom. The script provided is not working for me to connect via Powershell – Gvazzana Jan 16 '19 at 22:48
0

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://learn.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.

nsb53
  • 1
  • 1
0

You can access to your Azure Devops database with your Windows Authentication level. So your Windows login user should have configured sysadmin privilege in your database (e.g Microsoft SQL server)

TeachMeJava
  • 640
  • 1
  • 13
  • 35
Abed farvardin
  • 1
  • 1