Monday, May 25, 2020

IPython Notebook to connect to SQL Server 2019

I installed Anaconda 3 some time ago, and then I installed SQL Server 2019 without ML libraries for Python. Now I just wanted to connect from an IPython Notebook to this local named instance of SQL Server, directly.

I tried with both pyodbc (which worked from first try) and pymssql which seemed to have some issue. The error message was:

"MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (localhost\\SQLSRV2019)\n')"

It looks like pymssql library needs the TCP/IP connection configuration to be enabled, and also needs SQL Server Browser service to run. So, start SQL Server 2019 Configuration Manager and then Enable TCP/IP configuration setting:

And in Windows Services.msc make sure SQL Server Browser runs:

After these changes restart SQL Server 2019 instance (don't forget this step).

The code for pyodbc:
import pyodbc conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQLSRV2019;DATABASE=db1;UID=sa;PWD=***') cursor = conn.cursor() cursor.execute("select id1, name1 from Table1") rows = cursor.fetchall() print(rows)
The code for pymssql with Pandas DataFrame:
import pymssql import pandas as pd conn = pymssql.connect(user = 'sa', password = '***', host='.\SQLSRV2019', database = 'db1') q = pd.read_sql_query('select name1, id1 from Table1', conn) conn.close() df = pd.DataFrame(q, columns=['name1','id1']) print(df)
Then let's plot the values with matplotlib:
import matplotlib.pyplot as plt
You can try these with either JupyterLab / Jupyter Notebook from Anaconda 3 or even with VS Code extension for Anaconda.

Monday, February 10, 2020

Azure Key Vault—Private endpoints now available in preview

"Establish a private connection between Azure Key Vault and other Azure services by using Azure Private Link, now available in preview for all public regions.
All traffic to the service can be routed through the private endpoint, so no gateways, NAT devices, ExpressRoute or VPN connections, or public IP addresses are needed. Traffic between your virtual network and the service traverses over the Microsoft backbone network, eliminating exposure from the public Internet."

A question may be why only now? It should have been there from the beginning.

Docs link.