Skip to content

Latest commit

 

History

History
67 lines (46 loc) · 8.58 KB

sql-database-control-access.md

File metadata and controls

67 lines (46 loc) · 8.58 KB
title description services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
Granting access to Azure SQL Database and SQL Data Warehouse | Microsoft Docs
Learn about granting access to Microsoft Azure SQL Database and SQL Data Warehouse.
sql-database
sql-database
security
sql-data-warehouse
conceptual
VanMSFT
vanto
carlrab
craigg
10/05/2018

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.

Important

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.

Authentication

SQL Database supports two types of authentication:

  • SQL 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

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:

Next steps