Reputation: 43
I am trying to authenticate to my Azure SQL through c# using this code. The code works but I do not want to use my UserID and Password. Can I use anything else to authenticate? Token?
using System;
using System.Data.SqlClient;
using System.Text;
namespace sqltest
{
class Program
{
static void Main(string[] args)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "<server>.database.windows.net";
builder.UserID = "<username>";
builder.Password = "<password>";
builder.InitialCatalog = "<database>";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
Console.WriteLine("\nQuery data example:");
Console.WriteLine("=========================================\n");
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName ");
sb.Append("FROM [SalesLT].[ProductCategory] pc ");
sb.Append("JOIN [SalesLT].[Product] p ");
sb.Append("ON pc.productcategoryid = p.productcategoryid;");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
Console.ReadLine();
}
}
}
Upvotes: 4
Views: 1700
Reputation: 2978
As mentioned Managed identities is the way to accomplish this. Here is a similar post specific to App Service to Azure SQL this can also be accomplished via ARM and Managed Identities can also be granted access policies to Key Vaults.
Upvotes: 0
Reputation: 1182
You can use 2 things.
1. Use Managed Identities for Azure Resources
This is the recommended approach, in this method, code will generate a token using azure identities.
.NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method.
For More Information ->
https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql
2. Store sensitive information (username/password/connection string) in azure key vault
https://learn.microsoft.com/en-us/azure/key-vault/tutorial-net-create-vault-azure-web-app
Upvotes: 1
Reputation: 15619
You can configure and manage Azure Active Directory authentication with SQL. Refer to this document for the details.
Upvotes: 0