When develop software working with Azure SQL database, some special considerations needs to be taken into account.
-
Connection string
With standard SQL Server, if the connection didn’t specify a database name in your connection string, the connection default to master database. Unless specifically denyed access, a valid login with connection permission can connect to master database and be able to see the full list of database names on the connected instance, even if they don’t have permission to access those databases.
With Azure SQL database, unless specifically created a user for the login in master database, the login cannot connect to master database. In that case, if connection string didn’t specify the database name, it will still default to master database, but the connection will fail. Unlike above standard SQL Server, if the login is not associated with a user in the master database, it will not be able to see any other database the login has no access to, which is a good thing. Also, once connected, you can’t change context database to another database like with connection to standard SQL Server.
To make software compatible with both standard SQL Server and Azure SQL database, it might be better to always put the target database name in the connection string to start with, instead of leave it to later to change context. Avoid practice of switching database after connection made.
-
Using generic method when possible
Using generic method when possible, instead of simple function call that’s not support on Azure SQL. For example, we can using function DB_ID(‘dbname’) to check if a database existing on the instance. However, DB_ID() function only works for current database in Azure SQL database. When specify an name other than the current database, it will always return NULL in Azure SQL database. In this case, we might better query the sys.databases to check if the specific database exists or not.