CODEWITHSUNDEEP
azureazure-active-directoryazure-sql-databaseazure-functionsconnection-string
Sergio Solorzano
Sergio Solorzano

Reputation: 667

azuresql connection string-user auth not admin

The boilet plate connection string for sql azuredb uses the server's admin username/pwd. Using contained database a user can login to a specific db without needing server login access.

Can I setup the connection string of the database to the db user instead of having to use the server's admin user? I am using the connstring for an azure function to fetch data from db. Also, the user doesn't have an active directory account.

Thanks a bunch for your help

Upvotes: 0

Views: 483

Answers (2)

Thiago Custodio
Thiago Custodio

Reputation: 18387

If you want to restrict access to your SQL Database, you can also grant a Managed Identity to your function app, and grant access to it in the Sql Database. This way, only your function will be able to connect, and there's no risk of the connection string being leaked.

Here are good examples about how to do it:

https://www.azurecorner.com/using-managed-service-identity-in-azure-functions-to-access-azure-sql-database/

https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

Upvotes: 2

Jim Xu
Jim Xu

Reputation: 23111

If you want to use non-admin user to connect Azure SQL in Azure function, you can use the following connection string

Server=tcp:<sql server name>.database.windows.net,1433;
Initial Catalog=<databse name>;Persist Security Info=False;
User ID=<the non-adimn user name>;
Password=<passsword>;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

For example

  1. use SSMS to create non-admin user fro the database you need
 CREATE USER jim WITH PASSWORD = '<strong_password>'
 ALTER ROLE db_owner ADD MEMBER jim;
  1. Code. I use System.Data.SqlClient 4.6.1 to connect Azure sql in Azure function
public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            var constr = "Server=tcp:<sql server name>.database.windows.net,1433;Initial Catalog=<databse name>;Persist Security Info=False;User ID=<the non-adimn user name>;Password=<passsword>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            using (SqlConnection connection = new SqlConnection(constr))
            {
                var sql = "SELECT * FROM StarWars";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            log.LogInformation(reader.GetString(2));
                        }
                    }
                }


            }

...

}

enter image description here

enter image description here

update

If you want to use non-admin user to connect Azure SQL, please change the User ID value in the connecting string then save in application settings

  1. Configure connection string
Server=tcp:<sql server name>.database.windows.net,1433;
Initial Catalog=<databse name>;Persist Security Info=False;
User ID=<the non-adimn user name>;
Password=<passsword>;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

  1. Save in the application setting enter image description here

  2. Get it with code enter image description here

Upvotes: 1

Related Questions