Skip to content

Latest commit

 

History

History

azsqlmi-clr-demo

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
SQLMICLRDemo
SQLMICLRDemo
 
 
media
media
 
 
README.md
README.md
 
 

README.md

Create and Deploy CLR to SQL MI

05/14/2020
Carl Neal - Senior Cloud Solution Architect at Microsoft

Steps to Deploying a CLR Assembly to SQL MI

  1. Create the SQL MI Instance
    a. I enabled Public endpoint so I can work on this locally for my computer

    b. Go to the NSG and create an Inbound firewall rule to allow your Personal IP Address to Access on port 3342 (This allows remote connections from SSMS)

  2. Connect to the SQL MI Instance from SSMS v18.5
    a. Connection should look like > "sqlmi-samplename.public.f2a1e029dfs8251.database.windows.net,3342" > (make sure to add the ,3342 )

  3. Enable clr and strict security
    EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

    sp_configure 'clr enabled', 1 RECONFIGURE

    sp_configure 'clr strict security', 0 RECONFIGURE

  4. Confirm the SQL MI .Net Framework Runtime Version
    a. SELECT olm.[name], olm.[file_version]
    > FROM sys.dm_os_loaded_modules olm
    > WHERE olm.[name] LIKE N'%mscoreei.dll%';

  5. Create the database in SQL MI that you plan on deploying the CLR too

  6. Create a Visual Studio SQL Server Database Project

  7. Add a new SQL CLR C# item by Right Clicking VS Project -> Add -> New Item -> SQL CLR C# (or you can go to Project -> Add New -> Item from task bar)

  8. Write the appropriate code for your CLR

  9. Ensure that the target server SQL Server version matches the > database created above (Right Click Project -> Properties -> Project Settings)

  10. Confirm that the .NET Framework Version matches that of the SQL MI > Instance

  11. Publish the CLR to the SQL MI Instance (Right click -> Publish)
    a. Add the credentials to the Target Database connection and test > connection to confirm access (add the ,3342 to the connection > string)

  12. Confirm that the CLR Assembly was appropriately added to the SQL MI Instance

###To Deploy a CLR Assembly with External_Access###

  1. Make the database set to be Trustworthy in VS
    a. RC Project -> Properties -> Project Settings -> Database > Settings -> Misc Tab -> Check Trustworthy

  2. Make Database Trustworthy in SQL MI
    a. ALTER DATABASE [SQLMICLRDemo] SET TRUSTWORTHY ON