Upload files to MS SQL
Overview
On the MS SQL DBMS managed and DBA mode services the access to local disks is not allowed. Therefore the only way to access files from the SQL Server is to place them on a remote share on a VM member of the same resource domain as the SQL server, granting the appropriate rights to the SQL service account.
The upload files action eliminates the need of creating shares on other VM's, enabling the direct upload of certain files type to the SQL system.
The file transfer is done using an S3 bucket analogous to the Import DB functionality.
Target services
The feature will be available for Managed DBMS, Managed DBMS DBA mode and Managed SQL AlwaysOn. The table below illustrate which actions are possible depending on the service type. The differences in the purpose are due to the permissions set on the DBMS.
In DBA mode it is possible to enable xp_cmdshell and CLR as customers are sysadmin. This grant the capability to access files from the underlying file system or to load CLR assemblies.
| Service | Purpose | SSMS/Jobs | Portal |
|---|---|---|---|
| Managed MS SQL Server xxxx | Bulk insert | X | O |
| Managed MS SQL Server xxxx | Load assemblies, Load certificate | O | X |
| Managed MS SQL Server xxxx | Execute SQL scripts | X | O |
| Managed MS SQL DBMS xxxx DBA mode | Bulk insert | X | O |
| Managed MS SQL DBMS xxxx DBA mode | Load assemblies, Load certificate | X | X |
| Managed MS SQL DBMS xxxx DBA mode | Execute SQL scripts | X | O |
X = available
O = not available
The SQL AlwaysOn service is handled as the Managed MS SQL Server xxxx.
File types
The file types are restricted to the most commonly used in SQL.
The table below indicates which files are allowed on the a specific service type.
| Type | Extension | Purpose | Managed | DBA Mode |
|---|---|---|---|---|
| .xml | XML | Bulk insert | X | X |
| .fmt | Format file | Bulk insert | X | X |
| .txt | Text | Bulk insert | X | X |
| .csv | Comma separated value | Bulk insert | X | X |
| .sql | T-SQL script | SQL execution | X | X |
| .dll | Library | CLR Upload | X | X |
| .cer | SQL Certificate | SQL certificate backup | X | X |
| .key | SQL Key | SQL Key backup | O | X |
X = allowed
O = not allowed
For SQL certificates and keys there is not specific file extensions. In order to identify the files add _.cer and _.key as extensions for certificate and keys.
Upload repository
The files will be uploaded to this specific folder on the target system: D:\FileRepository. The repository folder cannot be changed.
The upload action includes an option to clean up the repository, i.e. delete the existing files and folders. If you don't want to clean up the repository the new files will be added, however files with the same name will be overwritten.
The child folder structure will correspond to the folder structure which is defined in the S3 bucket.
Restriction enforcement
Files which don't belong to the type listed above will be deleted as well as files without extensions.
A file rename in the repository is not possible.