John Liu Blog

Every drop counts

Azure SQL DB call external API

With the introducing of sp_invoke_external_rest_endpoint in Azure SQL DB, it’s possible to directly calling the Azure OpenAI service within the database. Azure OpenAI is in the safe-listed Azure services. For other API service (like common OpenAI) that’s not in the safe-list, you will need to create a wrapper API in the Azure API Management Instance. To invoke external rest endpoint in Azure SQL DB, a few setup steps are required:

Posted by John Liu Saturday, September 9, 2023

Power Platform ADFS Authentication

If you use ADFS authentication in Power Platform, you might “not” be able to create Office365 connection using a different account other than the currently login account via the normal way. When select “Use another account” option, after enter the second account email in the login prompt, it does not ask for password and automatically create the connection using your current login instead. The workaround is when the login prompt Window pop up, instead of enter the account details, copy the URL from the login address bar and paste the URL into Notepad.

Posted by John Liu Saturday, September 9, 2023

SQL Server Run Different Version of Python

SQL Server Machine Learning Service comes with a default Phyton runtime. For SQL 2019, it’s Phyton 3.5. You might need to run your Phyton script against a different version of Phyton runtime. To configure another Phyton version runtime to be available to your SQL instance, do the following: Install Phyton 3.10 with options to install for all users Install required packages Grant required permissions to SQL Server LaunchPad service account and ALL APPLICATION PACKAGES using icacls @ECHO OFF ECHO installing python "%~dp0python-3.

Posted by John Liu Thursday, August 17, 2023

Power BI Excel file

Excel can be used as data source for Power BI report. When the Excel file is on SharePoint or OneDrive, using SharePoint data source might have performance issue when it tries to navigate through the SharePoint folder/files. We can use Web data source to connect to the Excel file directly to eliminate the overhead with folder/file navigation. If you used the copy path option to get a direct link to the file from OneDrive/SharePoint you can use the URL as is.

Posted by John Liu Thursday, July 20, 2023

Azure Cosmos DB Emulator

We can use Azure CosmosDB emulator to simulate Azure CosmosDB without actually connect to Azure environment. The emulator is running inside a container. To setup the container using the latest image: docker run -it --name cosmosDB -p 8081:8081 mcr.microsoft.com/cosmosdb/linux/azure-cosmos-emulator This will create a container named cosmosDB and the emulator access port is 8081. When .Net code connect to the emulator, you might receive error about “The SSL connection could not be established” and “The remote certificate is invalid because of errors in the certificate chain: UntrustedRoot”.

Posted by John Liu Wednesday, July 19, 2023