Azure SQL Database and SQL Data Warehouse access control
To provide security, Azure SQL Database and SQL Data Warehouse control access with firewall rules limiting connectivity by IP address, authentication mechanisms requiring users to prove their identity, and authorization mechanisms limiting users to specific actions and data.
For an overview of the SQL Database security features, see SQL security overview. For a tutorial, see Secure your Azure SQL Database. For an overview of SQL Data Warehouse security features, see SQL Data Warehouse security overview
Firewall and firewall rules
Microsoft Azure SQL Database provides a relational database service for Azure and other Internet-based applications. To help protect your data, firewalls prevent all access to your database server until you specify which computers have permission. The firewall grants access to databases based on the originating IP address of each request. For more information, see Overview of Azure SQL Database firewall rules
The Azure SQL Database service is only available through TCP port 1433. To access a SQL Database from your computer, ensure that your client computer firewall allows outgoing TCP communication on TCP port 1433. If not needed for other applications, block inbound connections on TCP port 1433.
As part of the connection process, connections from Azure virtual machines are redirected to a different IP address and port, unique for each worker role. The port number is in the range from 11000 to 11999. For more information about TCP ports, see Ports beyond 1433 for ADO.NET 4.5 and SQL Database2.
SQL Database supports two types of authentication:
This authentication method uses a username and password. When you created the logical server for your database, you specified a "server admin" login with a username and password. Using these credentials, you can authenticate to any database on that server as the database owner, or "dbo."
Azure Active Directory Authentication:
This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. Use Active Directory authentication (integrated security) whenever possible. If you want to use Azure Active Directory Authentication, you must create another server admin called the "Azure AD admin," which is allowed to administer Azure AD users and groups. This admin can also perform all operations that a regular server admin can. See Connecting to SQL Database By Using Azure Active Directory Authentication for a walkthrough of how to create an Azure AD admin to enable Azure Active Directory Authentication.
The Database Engine closes connections that remain idle for more than 30 minutes. The connection must login again before it can be used. Continuously active connections to SQL Database require reauthorization (performed by the database engine) at least every 10 hours. The database engine attempts reauthorization using the originally submitted password and no user input is required. For performance reasons, when a password is reset in SQL Database, the connection is not reauthenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premises SQL Server. If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. A user with the
KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command.
User accounts can be created in the master database and can be granted permissions in all databases on the server, or they can be created in the database itself (called contained users). For information on creating and managing logins, see Manage logins. Use contained databases to enhance portability and scalability. For more information on contained users, see Contained Database Users - Making Your Database Portable, CREATE USER (Transact-SQL), and Contained Databases.
As a best practice your application should use a dedicated account to authenticate -- this way you can limit the permissions granted to the application and reduce the risks of malicious activity in case your application code is vulnerable to a SQL injection attack. The recommended approach is to create a contained database user, which allows your app to authenticate directly to the database.
Authorization refers to what a user can do within an Azure SQL Database, and this is controlled by your user account's database role memberships and object-level permissions. As a best practice, you should grant users the least privileges necessary. The server admin account you are connecting with is a member of db_owner, which has authority to do anything within the database. Save this account for deploying schema upgrades and other management operations. Use the "ApplicationUser" account with more limited permissions to connect from your application to the database with the least privileges needed by your application. For more information, see Manage logins.
Typically, only administrators need access to the
master database. Routine access to each user database should be through non-administrator contained database users created in each database. When you use contained database users, you do not need to create logins in the
master database. For more information, see Contained Database Users - Making Your Database Portable.
You should familiarize yourself with the following features that can be used to limit or elevate permissions:
- Impersonation and module-signing can be used to securely elevate permissions temporarily.
- Row-Level Security can be used limit which rows a user can access.
- Data Masking can be used to limit exposure of sensitive data.
- Stored procedures can be used to limit the actions that can be taken on the database.
- For an overview of the SQL Database security features, see SQL security overview.
- To learn more about firewall rules, see Firewall rules.
- To learn about users and logins, see Manage logins.
- For a discussion of proactive monitoring, see Database Auditing and SQL Database Threat Detection.
- For a tutorial, see Secure your Azure SQL Database.