Azure SQL server

About

Azure SQL is sql server as a service in Azure.

It has its own version

Concept

Azure SQL Server follows the concept of contained database (that is you can't make use of the use keyword to switch between database, you need to make another connections).

Management

Version

SELECT @@VERSION;
Microsoft SQL Azure (RTM) - 12.0.2000.8
	Jan 26 2018 23:35:00
	Copyright (C) 2017 Microsoft Corporation

User

For more information about accounts, see Managing Databases and Logins in Azure SQL Database

Admin

SQL Server administrator. As administrators, the server administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user.

Server Admin

Each Azure SQL server (which hosts a SQL Database or SQL Data Warehouse) starts with a single server administrator account that is the administrator of the entire Azure SQL server (known as the server admin)

Azure AD account

This principal is created as a contained database user in the master database.

Non-administrator users

Example:

CREATE USER userName WITH PASSWORD = 'pwd';
CREATE SCHEMA userName AUTHORIZATION userName ;
GRANT CONNECT TO userName ;
GRANT CREATE TABLE TO userName ;
GRANT CREATE VIEW TO userName ;
ALTER USER userName WITH DEFAULT_SCHEMA = userName;

Get certificate

To see the Azure SQL Server certificate

The port must stay at 443 (not at 1433)

openssl s_client -connect dbname.database.windows.net:443 -showcerts </dev/null 2>/dev/null | openssl x509 -outform PEM > dbcertfile.pem

Powered by ComboStrap