8

I need to access some part of SQL table data from c# console application.I need help to establish the server connection from c#.

DataBase Details:

 Server type : Database Engine
 Authentication : Active Directory-Universal with MFA support.

Also please do let me know How should I give my Connection properties?

Prathyusha Dwibhashi
  • 157
  • 1
  • 1
  • 10
  • How is the app going to be used? Most importantly, do you need an unattended login, and if you do, is a Service Principle login an option? – PerfectlyPanda May 21 '19 at 14:57

3 Answers3

5

If you don't want to fiddle with tokens or register your C# app as an Azure Application, you can use ODBC or OLE DB with the MSOLEDBSQL driver, which is able to use MFA / ActiveDirectoryInteractive authentication out of the box:

ODBC:

using System.Data.Odbc;
...
OdbcConnection con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};SERVER=sqlserver.database.windows.net;DATABASE=database;Authentication=ActiveDirectoryInteractive;UID=user@domain.com");

OLE DB:

using System.Data.OleDb;
...
OleDbConnection con = new OleDbConnection("Provider=MSOLEDBSQL;Data Source=sqlserver.database.windows.net;User ID=user@domain.com;Initial Catalog=database;Authentication=ActiveDirectoryInteractive");
Dan
  • 10,480
  • 23
  • 49
  • If anyone wondered how to use it with `OdbcConnectionStringBuilder`. You need to skip `{}` in the `Driver` - it won't work otherwise. See: https://learn.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcconnectionstringbuilder?view=dotnet-plat-ext-8.0#remarks – Mr Patience Mar 30 '23 at 12:57
3

There are 2 different scenarios for logging in to the database.

1) The user logs in with their account and you use that token to authenticate to the SQL database. In this case you can use the standard login popups which will handle the MFA piece for you.

2) Either the user doesn't have privileges on the DB (most standard web apps are an example of this) or you are creating an automated service that needs to log in to the DB. Since the reason for MFA is to have the user complete some action that a machine can't, like entering a code from their cell phone, unattended logins don't work with MFA.

If you are in the second scenario, you will need to create a service principle account that is not protected by MFA for the app to login. Instead of a user name and password, the app gets a unique appId and appSecret to use to access the database. You can add additional protection by placing the secret in Key Vault and limiting the access of that app to just the specific resources it needs to function.

Notice that in this case we aren't passing a user name and password in with the connection string. Instead we get the token separately before adding it to the connection.

string serverName = "myserver.database.windows.net"; 
string databaseName = "test";
string clientId = "xxxxxx-xxxxx-xxxxx-xxxx-xxxx"; 
string aadTenantId = "xxxxxx-xxxxxx-xxxxxx-xxxxxx-xxxxxxxx";
string clientSecretKey = "xxxxx/xxxxxx/xxxxx";

string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);

string AadInstance = "https://login.windows.net/{0}";
string ResourceId = "https://database.windows.net/";


AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
ClientCredential clientCredential = new ClientCredential(clientId, clientSecretKey);

DateTime startTime = DateTime.Now;
Console.WriteLine("Time " + String.Format("{0:mm:ss.fff}", startTime));

AuthenticationResult authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

DateTime endTime = DateTime.Now;
Console.WriteLine("Got token at " + String.Format("{0:mm:ss.fff}", endTime));

Console.WriteLine("Total time to get token in milliseconds " + (endTime - startTime).TotalMilliseconds);

using (var conn = new SqlConnection(sqlConnectionString))
{
     conn.AccessToken = authenticationResult.AccessToken;
     //DO STUFF
}
PerfectlyPanda
  • 3,271
  • 1
  • 6
  • 17
1

If you are using .net 5+ you won't have odbc, or oledb without adding a nuget package. Often you just need to switch from

using System.Data.SqlClient; to using Microsoft.Data.SqlClient;

And use a connection string like shown in the docs

// Use your own server, database, and user ID.
// User ID is optional.
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb; User Id=user@domain.com";

using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
    conn.Open();
}

// User ID is not provided.
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb";

using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
    conn.Open();
}
sec0ndHand
  • 430
  • 4
  • 8