Getting C# Jupyter Notebooks running on Ubuntu 18.04

Why C# on Jupyter Notebooks?

I’ve heard of Jupyter, or Jupyter-like notebooks often. I've briefly evaluated the SQL Server notebooks in Azure Data Studio, and often hear of them used in data science.

The primary notebook platform, Jupyter notebook, only supports Python, and I haven’t yet invested in learning that language. This has previously been a stumbling block for me learning further about notebooks.

At a recent SQL Saturday precon, notebooks were heavily used as a part of the training material, and I got to experience enough to understand the benefit of using notebooks: annotated, byte-size, interactive, hands-on documentation.

I've been planning an introduction presentation to Azure Cosmos DB for our web developers, and Jupyter notebooks seem to be a great fit for that demo, with the exception that Jupyter notebooks use Python, not C#, the developers' native language. Why not use Visual Studio / VS Code? I don't want the web developers to be judging my console application development abilities, I want them to see how the Cosmos DB client can be interacted with in C#.

Azure Cosmos DB has a C# Notebook sample, but that appears to be broken at present, and I can’t work that out - but the idea is simple enough - it is just a notebook running C#. After a little searching on Google, I found that the .NET team have put out instructions on getting a C# kernel (runner) installed in standard Jupyter Notebooks. Here’s the steps I followed to get that up and running.

Steps to creating a C# Jupyter Notebooks environment running on Ubuntu:

Download and Install Anaconda

• Download the Anaconda installer from https://www.anaconda.com/distribution/#linux (I chose the Python 3.7 installer) 
• Make the script executable: Right-click on the .sh installer file, and select Properties > Permissions > Allow executing file as program. 
• Open Terminal, navigate to the folder containing the Anaconda installer, and execute the .sh file 
• I ran the two commands given as optional in the Anaconda installer. 
• Run jupyter notebook from Terminal. All well, Jupyter Notebook will load. 
• Select New, and note that only “Python 3” is an option. Additional engines, called “kernels” can be installed to add additional languages to this list. 
• In Terminal, press CTRL+C to kill Jupyter Notebook.

Install dotnet interactive and enable C# and PowerShell support to Jupyter Notebooks

• Install the .NET 3.1 SDK by following the Register Microsoft Key and Feed and Install the .NET Core SDK steps at Install .NET Core on Ubuntu 18.04 package manager - .NET Core 
• Run the command to install dotnet interactive at dotnet/interactive 
• Run the command to add the C# and Powershell kernels to Jupyter
• Notebooks ( dotnet interactive jupyter install ) 
• Check the C# and Powershell kernels have been added with the command jupyter kernelspec list 
• Run the command jupyter notebook from Terminal again 
• Select the New menu item, and you should now see “.NET (C#)” as an option, select this. 
• Enter Console.WriteLine("Hello from C#!"); into the box beside In [ ]:, and click the Run button. 
• The In [ ]: text should change to In [ * ]: text to indicate that block is now running.

You now have a C# Jupyter Notebook running in Ubuntu. Switch to Powershell, replace that sample with Write-Host("Hello from PowerShell!");

There we have it - below is a screenshot of the C# tutorial Tutorial: Build a .NET console app to manage data in Azure Cosmos DB SQL API account running successfully from a Jupyter notebook.

Solution to SQL Agent Job "Error retrieving WMI variable WMI(TextData)"

I've been trying to implement SQL Agent Alerts on disk growth for a day now.

I've followed a few different guides on WMI alerting, but every time the alert fires, the SQL Job fails with the below error, and trying to play with different methods to ESCAPE the WMI tokens was unfruitful.

Unable to start execution of step 1 (reason: Error retrieving WMI variable WMI(variable name here): 0x80041002).  The step failed.

SQL Junkie Share's blog had the answer. I needed to go to my SQL Agent properties, and under Alert System tick "Replace tokens for all job responses to alerts". Thanks Akhil!

Adding an identity column or column with a default constraint to a table that already has data

Today I had a coworker ask me to review code that had a select statement moving data out of a table, truncating the table, adding an new column with identity and a default constraint, then re-inserting the data into the table. His explanation was that he couldn't add an identity column or a column with a default constraint to a table that contained data.

I displayed to him that moving data out of the table isn't needed to add either a default constraint or identity.

Here's an example of how to do that:

Create a table containing existing data:

create table Customer (
Firstname nvarchar(200)
, Surname nvarchar(200)
)

insert into customer(
firstname
, surname
)
select 'Bill', 'Palmer'
union all select 'Sarah', 'Moulton'
union all select 'Wes', 'Davis'

select firstname
, surname
from customer

There's no problem with adding an identity column to an existing table, except perhaps the time it takes to run this statement - keep that in mind if you're dealing with a very large table.

alter table customer add id int identity(1,1)

select id
, firstname
, surname
from customer

That query will succeed, and return the results of a select statement that includes the new ID column, with values!

Adding a column with a default constraint to an existing table requires a little trick. For this example, let's assume we want to add a bit column to customer, indicating whether the customer is a VIP. 

If you do the usual command to add a new column with a default constraint, as below, you may be surprised.

alter table customer add isVIP bit default(0)

select id , firstname , surname , isVIP
from customer

Adding the default constraint will succeed, but the returned result-set will have null values in the "isVIP" column for the existing rows. To populate existing rows with the default value, run this statement instead:

alter table customer add isVP bit default(0) not null

select id
, firstname
, surname
, isVP
from customer

The existing rows with be populated your default value, and return that value in your result-set. 

If you want this column to allow NULL values, run the below statement to set the column to nullable, keeping the recently created default constraint.

alter table customer alter column isVP bit null

Identifying unencrypted SQL connections

I'm working on implementing encrypted connections within my team to protect against packet sniffing, however a few developers seem behind on implementing encrypted connections. The below query helped me identify those developers for follow-up, and gave me the information they needed to identify which application was making the unencrypted connections.

select sessions.login_name
, sessions.program_name
, sessions.host_name
, sessions.login_time
, sessions.last_request_start_time
, sessions.status
, sessions.cpu_time
from sys.dm_exec_connections as connections
  inner join sys.dm_exec_sessions as sessions
  on connections.session_id = sessions.session_id
where connections.encrypt_option = 'FALSE'
order by sessions.login_name

Resolving "Cannot create an instance of OLE DB provider "OraOLE.DB.Oracle" after changing the SQL Server Agent account.

After an SQL Server Agent service account change, scheduled jobs that call an Oracle linked server began failing with the error

Executed as user: <username>. Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7302).  The step failed.

The stored procedures called by those jobs executed as expected when I executed them from my account.

No ACCESS DENIED issues came up in Process Monitor while attempting to make the connection.

After some websurfing, this MSDN blog suggested modifying the MSDAINITIALIZE security settings to give the agent account all permissions on both Launch and Activation Permissions, Access Permissions and Configuration Permissions.

After doing that, the error changed to

Executed as user: <username>. The OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name> reported an error. Access denied. [SQLSTATE 42000] (Error 7399)  Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7350).  The step failed.

Progress! Searching for that error message took me to this SQL Server Central post, where a user suggested ticking "Allow inprocesses" for the Oracle linked server provider.

Success! The job now completed as expected.

ReportServer.dbo.ExecutionLogStorage table only shows a couple of months back

In my workplace we use the reports based off the SSRS Reporting Services database ExecutionLogStorage to identify report usage and alert us to report execution failures, in the form of the below statement to identify how often different reports have been used, how long they take to run, and how often they fail:

select Catalog.path, ExecutionLogStorage.*
from ExecutionLogStorage
inner join Catalog
on ExecutionLogStorage.ReportID = Catalog.ItemID

Unfortunately, on a server that went online some-time last year, the report execution logs are very limited. After doing some research I found the answer here (Microsoft TechNet article).

If the query below returns anything other than -1, your report execution logs will be being deleted after the resulting number of days.

select *
from ConfigurationInfo
where name = 'ExecutionLogDaysKept'

If it is, update it to -1 to stop historical logs from being deleted.

As to how to restore the deleted ExeuctionLogStorage entries, you'll need to restore a copy of the database to a temporary location, and run insert operations to re-add those rows to the table.

Users cannot search for Reports on SSRS 2012 even with the System Administrator role

Today I encountered an issue where domain users could not search for reports on a recently comissioned SSRS instance. The users had the System Administrator roles in SSRS, but received the error “The permissions granted to domain\username are insufficient for performing this operation. (rsAccessDenied)” when attempting to search or access the Reporting Services home page (http://reportserver/reports). When the user was added to the server’s local Administrator group (BUILTIN\Administrators) the issue did not occur.

The Reporting Services log file had an error beginning with the following added:

“Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User domain\username does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.   at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)”

Disabling UAC, as blog posts suggested, would require a restart to check, and the previous server had UAC set to its default and was working as expected.

The users had browser role on each of the folders on the home page.

The solution was to give the users the browser permission on the home page. Turns out, that home page is a folder, with its own security, and without the browser role on the Home folder, they can’t view the home page or search for reports.