Joel Greijer

  $ErrorActionPreference = "stop" $server = "your-server.database.windows.net" $database = "your-database" $query = "select 1 from dbo.yourtable" Connect-AzAccount $token = ( Get-AzAccessToken -ResourceUrl "https://database.windows.net" ).Token Invoke-Sqlcmd -server $server -Database $database -Query $query -as dataset -AccessToken $token write-output "Done!"

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...

It's very tricky to find the schema of the crucial .configsettings file that is used when automating deployments of ssas projects.  It has come to my understanding that msbuild does not produce this file for Tabular Model projects. Though in some cases Microsoft forget to mention this in their guides.  I know clarification is pending on this page https://docs.microsoft.com/en-us/analysis-services/deployment/deployment-script-files-solution-deployment-config-settings?view=asallproducts-allversions Meanwhile. Here is a sample of the file in case you need it <ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http:...

Since last update to Version: 2.85.681.0 64-bit (september 2020) 100% fail rate connecting to other than your local SQL server instance. Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )" This happens With encryption Without encryption Authentication: Windows  Authentication: Microsoft account Solution Download August 2020 version and install Microsoft hides older versions as last one becomes available so you have to get them from someone who saved it. I have the 64 bit version. Contact me if you need it.

;WITH unicodeDataSource AS (   SELECT N'Hello' AS Text , N'English' AS Language UNION ALL   SELECT N'Здравствуйте' AS Text, N'Russian' AS Language UNION ALL   SELECT N'こんにちは' AS Text, N'Japanese' AS Language UNION ALL   SELECT N'Hallå' AS Text, N'Swedish' AS Language  ) SELECT *,    iif( s.Text = cast(cast(s.Text AS varchar(100)) AS nvarchar(100))      ,1,0) AS CastWasSuccessful FROM unicodeDataSource s Result Text Language CastWasSuccessful Hello English 1 Здравствуйте Russian 0 こんにちは Japanese 0 Hallå Swedish 1

replace(substring((dt_str,30, 1252) getdate(), 1, 23)," ","t")

Yes, you can create circular precedence constraint chains in SSIS packages. At least if you automate! Using BIML (which has no check for stupidity). It does what it's told. So investigate your generated packages carefully. Probably your mysteriously failing master package arrived at this if you have the same symptoms as I had When running from Sql Server Agent : Unexpected Termination (No messages) When running in Visual Studio : Starting / Cancelled in output window. Hanging of Visual Studio Windows log  Faulting application name: devenv.exe, version: 16.2.29306.81, time stamp: 0x5d72c031 Faulting module name: DTS.dll, version: 2019.150.1301.433, time stamp: 0x5d1ee90e Exception code: 0xc00000fd 0xc00000fd  seem to indicate buffer overflow (infinite loop clue!) Update: This is reported by ClemJax in  https://github.com/varigence/BimlPit/issues/98