4 October 2017
4 October 2017

How to deploy changes to Azure SQL Database using SQL Compare and Azure Active Directory

Microsoft introduced Azure Active Directory (AAD) as a secure way to manage credentials for access to the different offerings available on the Azure platform, including Microsoft Azure SQL Database. It integrates with existing on-premise Windows Server Active Directory, so users require only a single Windows credential for secure access to both on-premise SQL Server instances, and Azure SQL Database instances.

This quick tip shows how to connect to Azure SQL Database instances from SQL Compare, using its built-in Active Directory authentication mode, giving you an easier and more secure way to, for example, deploy schema changes from version control to an Azure SQL Database. You’ll need to be using SQL Compare 12 (v12.4.9) or later.

Why AAD authentication?

Prior to AAD, you couldn’t use Windows authentication to access a database in Azure; you had to provide a SQL Server login and password, and the SQL Server accounts and passwords are stored and managed within SQL Server. This has obvious security implications from the DBA’s perspective. For example, you’ll need strong checks and procedures in place to guard against users or third-party tools providing weak or blank passwords. It also means that you’re sending passwords across the network, for authentication.

Use of Windows authentication is more secure and considered a best practice for SQL Server access control. Windows-authenticated logins can take advantage of the Windows and Active Directory mechanisms for protecting user credentials, such as the Kerberos protocol or Windows NT LAN Manager (NTLM). Also, Windows Authentication uses encrypted messages to authorize access to SQL Server, rather than passing passwords across the network.

When used in conjunction with Active Directory, it also makes account management much easier and more secure, since we can exploit AD’s group and user account administrative and password management capabilities. A DBA can implement and manage a “least-privilege” permission system through AD, assigning user accounts to the correct database role with only the minimum necessary permission set for the required task, which in turn is a member of an AD group.

The official documentation is a good place to start if you would like to learn more about Azure Active Directory Authentication for SQL Database.

Using AAD with SQL Compare

If you already use Azure Active Directory to manage permissions in your SQL Database instances, it’s simple to get started using SQL Compare. Assuming the current user, running the comparison project, is registered in AAD, you can simply select “Active Directory integrated authentication” from the Authentication dropdown in the connection dialog.

To use a different Active Directory user to connect, select “Active Directory password authentication” and enter the username and password.

Then hit “Compare now” to compare and deploy differences in your databases!

Conclusion

Azure Active Directory support, introduced with SQL Compare 12, will make it easier and more secure to compare and deploy changes to Azure SQL Database.

We’re always looking for ways to improve the experience of using SQL Compare. If you have any feedback or suggestions for new features let us know on uservoice, and if you’d like to find out more about what we’re working on now and what’s coming up next, check out our roadmap.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    How to compare two DacPacs using SQL Compare

    Often during my development work, especially at the Proof-of-Concept (PoC) stage, I need a quick and easy way to save my current progress, for example, by updating a set of object build scripts in a directory. Ultimately, I also need a way to compare my current development database or script directory to a target production

  • Article

    How to build multiple database versions from the same source: post-deploy scripts

    This is the second post in a three-part series that explains how to use SQL Compare to maintain a single source of truth in version control for a SQL Server database which can be deployed to multiple production instances, despite the fact that some production instances have customizations. My previous article explained how to use SQL

  • Article

    How to create a directory of object-level scripts using SQL Compare

    In this simple ‘how to’ article, I’ll explain why it’s often useful to be able to create a directory of object-level scripts from an existing database, and then how to create that directory of scripts using SQL Compare. For each object in the database there will be one object build file (*.sql). The scripts will

  • Article

    Deploying schema changes to multiple databases using the SQL Compare command line

    Sometimes, you need to synchronize multiple target databases with the same source database, or with the source code for that database, in version control. You may need to push out the schema changes to ten copies of the database on ten different servers, as part of a deployment, for example. Or there may be cases

  • Article

    How to deploy a database plus static data using SQL Compare and SQL Data Compare

    We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if we want to include static data in the build? This article describes how to the use the SQL

  • Forums

    SQL Compare Forum

    Forum for users of SQL Compare schema synchronization utility