OAuth Authentication Fails for Azure SQL Server - Login Error for User

Rohini Prabhakar Gohate 0

Description:

I am trying to implement OAuth authentication for an Azure SQL Server database using the Go (go-mssqldb) driver. I have successfully retrieved the access token using the following credentials:

Client ID
Client Secret
Tenant ID

However, when I try to connect to the Azure SQL Server with the token, I encounter the following error message:

2024**/09/10 15:06:13 Error pinging the database: mssql: login error: Login failed** for user ''****. exit status 1

It seems that the user information is either missing or not being set correctly, even though I have the access token.

My Setup:

Go version: go1.20.4 linux/amd64
go-mssqldb version: v1.7.2
OAuth flow: Client credentials (client ID, secret, tenant ID)

Questions:

Does the go-mssqldb driver support OAuth authentication for Azure SQL Server?
If yes, what is the correct way to pass the access token for authentication in this scenario?
Am I missing any specific configuration or setup steps for OAuth authentication with Azure SQL Server?

Any guidance or solutions would be appreciated!

2 answers

Erland Sommarskog 111.6K Reputation points MVP

2024-09-12T21:20:12.32+00:00

How did the command line where you tried to use the access token look like?

Browsing help, I did not see anything that seemed to match, so the answer may be that it is not possible.

The Github repo for go-sqlcmd is at https://github.com/microsoft/go-sqlcmd I could not find any issues related to OAuth there.

I will need to confess I don't know much about OAuth, but I know that the OLE DB driver supports an AccessToken option for connection, and I guess this is related to what you are trying to do. (But OLE DB has no relation to go-sqlcmd.)
Please sign in to rate this answer.
Amira Bedhiafi 24,711 Reputation points

2024-09-12T21:23:49.51+00:00

I have a doubt he means SQL Server on Azure
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Amira Bedhiafi 24,711 Reputation points

2024-09-12T21:22:29.7566667+00:00
To address your questions:

Driver Support for OAuth: Yes, the go-mssqldb driver supports OAuth authentication. You can use an access token to authenticate to an Azure SQL Server database.

Passing the Access Token: In your connection string, you need to provide the access token using the accesstoken parameter rather than the standard username and password. Here's a general approach:

First, ensure you've obtained the access token correctly using the client_id, client_secret, and tenant_id.

Then, pass the token as part of the connection string when establishing the connection. For example:

accessToken := "your-access-token-here" connString := fmt.Sprintf("server=%s;database=%s;accesstoken=%s", "your-server.database.windows.net", "your-database", accessToken) db, err := sql.Open("sqlserver", connString) if err != nil { log.Fatalf("Error opening database connection: %v", err) } err = db.Ping() if err != nil { log.Fatalf("Error pinging database: %v", err) }

Missing Configuration:

Ensure the access token is valid and has the correct scopes for Azure SQL Database access. You should request a token for the audience/resource https://database.windows.net/.

Ensure that the application you're authenticating with has been granted the appropriate database permissions.

Double-check that you're correctly formatting and passing the access token. Since this is an OAuth flow, passing an incorrect or expired token could result in the login error.
Please sign in to rate this answer.
Erland Sommarskog 111.6K Reputation points MVP

2024-09-12T21:37:15.5966667+00:00

Ah, good point. I was thinking of the command-line tool only. But now I recall that the driver is available standalone, and that was actually what Rohini was asking about.

Rohini Prabhakar Gohate 0 Reputation points

2024-09-13T08:55:20.35+00:00

Hi @Amira Bedhiafi

Yes I have already tried with access token also but with that also I got same error.

This is the code and output:

package main import ( "context" "database/sql" "fmt" "log" _ "github.com/denisenkom/go-mssqldb" "golang.org/x/oauth2/clientcredentials" ) // Azure AD credentials const ( clientID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx" clientSecret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" tenantID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx" authority = "https://login.microsoftonline.com/" resource = "https://database.windows.net/" ) // Function to get OAuth2 Token func getOAuthToken() (string, error) { ctx := context.Background() // Set up OAuth2 client credentials config := &clientcredentials.Config{ ClientID: clientID, ClientSecret: clientSecret, TokenURL: fmt.Sprintf("%s%s/oauth2/v2.0/token", authority, tenantID), Scopes: []string{resource + "/.default"}, } // Get token token, err := config.Token(ctx) if err != nil { return "", err } fmt.Printf("Access Token: %s\n", token.AccessToken) return token.AccessToken, nil } func main() { // Get OAuth2 token accessToken, err := getOAuthToken() if err != nil { log.Fatalf("Failed to get token: %v", err) } // Build connection string for Azure SQL Server with access token authentication connString := fmt.Sprintf("server=%s;database=%s;accesstoken=%s", "xxxxx.database.windows.net", "testDB", accessToken) // Open connection db, err := sql.Open("sqlserver", connString) if err != nil { log.Fatalf("Error opening database connection: %v", err) } defer db.Close() // Test the connection err = db.Ping() if err != nil { log.Fatalf("Error pinging database: %v", err) } else { fmt.Println("Successfully connected to the database!") } // Test query to ensure connection is successful rows, err := db.Query("SELECT * FROM cars") if err != nil { log.Fatalf("Query failed: %v", err) } defer rows.Close() // Print results for rows.Next() { var col1 string var col2 int err := rows.Scan(&col1, &col2) if err != nil { log.Fatalf("Failed to scan row: %v", err) } fmt.Printf("Result: %s %d\n", col1, col2) } if err := rows.Err(); err != nil { log.Fatalf("Row error: %v", err) } } Output: Access Token: eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Ikg5bmo1QU9Tc3dNcGhnMVNGeDdqYVYtbEI5dyIsImtpZCI6Ikg5bmo1QU9Tc3dNcGhnMVNGeDdqYVYtbEI5dyJ9.eyJhdWQiOiJodHRwczovL2RhdGFiYXNlLndpbmRvd3MubmV0LyIsImlzcyI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0L2NkZTZmYTU5LWFiYjMtNDk3MS1iZTAxLTI0NDNjNDE3Y2JkYS8iLCJpYXQiOjE3MjYyMDc4MDMsIm5iZiI6MTcyNjIwNzgwMywiZXhwIjoxNzI2MjExNzAzLCJhaW8iOiJFMmRnWUdnK1BIbldpbSs5bG02Ym1FMFAvWEE4Q2dBPSIsImFwcGlkIjoiZTQ0ODFlYzQtNzIwYS00YjgxLThiYmQtODk3ZWMxYmM3NzA5IiwiYXBwaWRhY3IiOiIxIiwiaWRwIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvY2RlNmZhNTktYWJiMy00OTcxLWJlMDEtMjQ0M2M0MTdjYmRhLyIsImlkdHlwIjoiYXBwIiwib2lkIjoiZGZiOGUwZTUtYjJiMC00MGJmLThhZTQtNDg0NjRlMjc3M2NjIiwicmgiOiIwLkFTd0FXZnJtemJPcmNVbS1BU1JEeEJmTDJ0TUhLUUliRF9kSXV0d2JwcXVyYldZc0FBQS4iLCJzdWIiOiJkZmI4ZTBlNS1iMmIwLTQwYmYtOGFlNC00ODQ2NGUyNzczY2MiLCJ0aWQiOiJjZGU2ZmE1OS1hYmIzLTQ5NzEtYmUwMS0yNDQzYzQxN2NiZGEiLCJ1dGkiOiJ4U2tZR1VJczVraWJ6ZlMzNW9CREFBIiwidmVyIjoiMS4wIiwieG1zX2lkcmVsIjoiNyA4In0.XI6A5HRJlsFLmVjASQX0DO5WwSNPuhlXGAMlDphIc9AyK8D8JMnGuJI3Jwigow42Tj73lVg0gmK_R09b0YuLfbqHJeim7b27p_Nvg7KmSijJvRuyUXyXnGwWRAtId9DsSqo--zjOSqHfMUtlSREcPjfvk2IEHcoIbKhHV-UXkPmphiU-XoCqQ5K3pqwCwyUhKPGjOTAEDTnHTzX9RtfcR-6mNHg_EEQXuiiTfu7D0vYHfymm5VH2WfmOJgF5ybzgRrXzRL36czfPonREfLQiAvXc4Uur8re_wZ69lUoYYtqtoXJTRF0L9rWVk4QLtfEbl1nZ97oVQWENnOmPh3IFNQ 2024/09/13 11:45:05 Query failed: mssql: login error: Login failed for user ''. exit status 1

Can you please help me with what missing configuration will I need or does any permission we need to give to user if yes exactly how we needs to configure?

Any leads would be very helpful. Thank you.

Rohini Prabhakar Gohate 0 Reputation points

2024-09-13T11:55:26.4466667+00:00

I have an app registration named TibcoSQLServer for my Azure SQL Server, and I need assistance in configuring the appropriate API permissions for this application. Could you please advise on which API permissions need to be selected for allowing the application to access the Azure SQL Server?

Additionally, I tried running the following command but encountered a permission issue:

bash Copy code sqlcmd -S xxxxx.database.windows.net -d master -U azureuser -P xxxxx

I executed the following query to check the roles for the user azureuser:

sql Copy code SELECT

The result showed no rows, so I attempted to add the user to the db_datareader role with the following query:

sql Copy code ALTER

However, I received the following error message:

arduino Copy code Msg

Correct me if I'm doing anything wrong.

Also, another question: does the Microsoft ODBC Driver 18 for SQL Server support OAuth authentication? If so, could you provide guidance or documentation on how to configure OAuth for this driver?

Your help would be greatly appreciated.

Thank you.

Rohini Prabhakar Gohate 0 Reputation points

2024-09-13T12:07:43.9633333+00:00

Please check this for commands:

Additionally, I tried running the following command but encountered a permission issue:

sqlcmd -S xxxx.database.windows.net -d master -U azureuser -P xxxx

I executed the following query to check the roles for the user azureuser:

SELECT`` dp.name ``AS`` RoleName, dp2.name ``AS`` UserName ``FROM`` sys.database_role_members ``AS`` drm ``INNER`` ``JOIN`` sys.database_principals ``AS`` dp ``ON`` drm.role_principal_id ``=`` dp.principal_id ``INNER`` ``JOIN`` sys.database_principals ``AS`` dp2 ``ON`` drm.member_principal_id ``=`` dp2.principal_id ``WHERE`` dp.name ``=`` ``'db_owner'`` ``AND`` dp2.name ``=`` ``'azureuser'``;

The result showed no rows, so I attempted to add the user to the db_datareader role with the following query:

ALTER`` ROLE db_datareader ``ADD`` ``MEMBER`` [azureuser];
However, I received the following error message:
Cannot alter the role ``'db_datareader'``, because it does ``not`` exist ``or`` you ``do`` ``not`` have permission.

Erland Sommarskog 111.6K Reputation points MVP

2024-09-13T21:25:27.11+00:00

The error message about db_datareader suggests that you are connected with a user who does not have permission to change role membership. You should have a user that is in the db_owner role to do this.

I looked at the connection-string keywords for ODBC 18, and I was surprised not to see AccessToken there. I note that with OLE DB, it actually has a space: Access Token. But that is OLE DB.

Also, I took the liberty to edit your posts to clear out server names and passwords.

Erland Sommarskog 111.6K Reputation points MVP

2024-09-14T08:33:38.35+00:00

Oh, by the way, have you enabled your Azure SQL Server for entra authentication?

Also, in your post above, there seems to have been some accidents when wanted to include SQL code:

The WYSIWIG editor does not always work well when you try to edit code snippets, so you may have to switch to Markdown mode to get it right.

As for your questions of API permissions, I'm afraid that is out of my league.

Erland Sommarskog 111.6K Reputation points MVP

2024-09-14T11:48:01.5566667+00:00

Looked a little more at ODBC, and it seems that there is a connection attribute: https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver15#sql_copt_ss_access_token. However, there does not seem to settable through the connection string, but you would have to set it separately.

Rohini Prabhakar Gohate 0 Reputation points

2024-09-16T07:42:38.6666667+00:00

Should I enable Entra authentication for my Azure SQL Server? If so, could you guide me through the steps to configure it?

Additionally, I’m trying to configure API permissions for my app registration TibcoSQLServer. Could you suggest which API permissions are required for the app to interact with Azure SQL Server?

Erland Sommarskog 111.6K Reputation points MVP

2024-09-16T21:20:33.1666667+00:00

Yes, you should enable Entra. That is a prerequisite. You do this on the blade for the Server itself. There is an option under Settings:

When it comes to the API permissions, I will need to pass. As I said, that is out of my league.

Rohini Prabhakar Gohate 0 Reputation points

2024-09-17T09:51:38.97+00:00

Ok.

@Amira Bedhiafi, Can you please help me with how to configure API permissions for my app registration TibcoSQLServer. Could you suggest which API permissions are required for the app to interact with Azure SQL Server?

Rohini Prabhakar Gohate 0 Reputation points

2024-09-17T09:53:56.6133333+00:00

Hi @Erland Sommarskog , Do I need to add user (set admin)under Microsoft Entra ID? and while adding user that means user will be rgohate@****.com? Because I'm not seeing any "azureuser" as a user in a list. Sorry but this confusing.

Correct me if I'm wrong.

And in Screenshot where you have pointed I have make the status of DB - online which was previously paused.

Would be very helpful if you give any input to this.
Thanks

Oury Ba-MSFT 19,176 Reputation points Microsoft Employee

2024-09-18T19:36:26.48+00:00

@Rohini Prabhakar Gohate

You can follow the detailed tutorial on Microsoft Learn to set this up.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

OAuth Authentication Fails for Azure SQL Server - Login Error for User

2 answers

Your answer