Azure Database creation using T-SQL or SSMS currently allows Unsupported characters in it's name.

[Guest NithyaBon]

Issue:

 

ARM (Azure Resource Manager) API does not support specific T-SQL resource naming formats and that's why when Databases are created using T-SQL/SSMS, unsupported characters could get allowed into the names.

Azure Portal UI does not have this issue, because when you use Portal UI for creating a Database, the creation goes through ARM, and hence when unsupported characters are entered in the Name field, it immediately throws error, as shown in the below image.

 

 

 

Therefore, this issue of allowing unsupported characters in the DB name is present in one specific create scenario: i.e., when customer creates database using SSMS/T-SQL command. As this create does not go through the ARM, hence it’s not being restricted by ARM rules.

 

 

 

 

 

Details:

Officially an Azure SQL Database resource name should not contain certain symbols:

Resource naming restrictions - Azure Resource Manager

 

- Can't use: < > * % & : \ / ? or control characters

 

- Can't end with period or space.

 

But not all of this restrictions are Applied when database is created using T-SQL command from master database. This is done to preserve consistency between on-prem Sql Server and Azure Sql databases. Database Identifiers - SQL Server

 

 

 

• However, Database should be visible through SSMS and you can do any operations those that can be done from T-SQL.
  
• As from Azure perspective:
  

1. You can see database in list of all databases in a server (Portal page with list of all databases in a specific server)
   
2. You cannot open specific page to any database that have $ in the name
   
3. This database will not appear in ARM cache or Azure Resource graph
   
4. Any Powershell/Azure CLI/Portal operation on this specific database will fail.
   
5. You will not see database in list of all databases (Database browse list page)
    
   

 

Workaround for customers:

 

Database is still operational from T-SQL perspective, but if you want to be able to manage it from outside of T-SQL, then the only way is to rename database using T-SQL command to not have the unsupported characters in it's name:

 

Rename a Database - SQL Server

 

Continue reading...