#StackBounty: #ubuntu #sql-server Unable to configure/start mssql-server

Bounty: 50

I get an error after following this guide: https://computingforgeeks.com/how-to-install-ms-sql-on-ubuntu-18-04-lts/ and running the configuration command.

I’ve searched for a couple hours trying to find the specific issue I have but I can’t seem to find anything.

I believe it’s something to do with me running Ubuntu 18.10 because installing mssql inside a docker container (as per official M$ guide) does not work either.

The installation itself goes smooth but I get an error at the end of the configuration command and manually starting the mssql service just makes it get a failed status.

Output of the installation command:

The following NEW packages will be installed:
  mssql-server
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/178 MB of archives.
After this operation, 927 MB of additional disk space will be used.
Preconfiguring packages ...
Selecting previously unselected package mssql-server.
(Reading database ... 167661 files and directories currently installed.)
Preparing to unpack .../mssql-server_14.0.3076.1-2_amd64.deb ...
Unpacking mssql-server (14.0.3076.1-2) ...
Processing triggers for libc-bin (2.28-0ubuntu1) ...
Setting up mssql-server (14.0.3076.1-2) ...
Processing triggers for man-db (2.8.4-2) ...
Processing triggers for libc-bin (2.28-0ubuntu1) ...

Output of the configuration command:

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
This program has encountered a fatal error and cannot continue running at Fri Apr  5 15:01:09 2019
The following diagnostic information is available:
       Reason: 0x00000004
      Message: RETAIL ASSERT: Expression=(false) File=Thread.cpp Line=4781 Description=Timed out waiting for thread terminate/suspend/resume.
   Stacktrace: 000000006ad31773 000000006ad2ad0a 000000006ac4b8b1
               000000006ac49dc5 000000006ac02f19 000000006ad6cf48
               00000002809328df
      Process: 11792 - sqlservr
       Thread: 11904 (application thread 0x1a0)
  Instance Id: 75c7c1b5-ce5f-4436-8acc-a2cdc97b6c0a
     Crash Id: 40695f67-2869-4316-b6e3-786530d7123c
  Build stamp: 70437f6583b8ef39b1ef70539ef84690980315dc7a4436c9c40015f28610e4aa
 Distribution: Ubuntu 18.10
   Processors: 2
 Total Memory: 4136767488 bytes
    Timestamp: Fri Apr  5 15:01:09 2019
Ubuntu 18.10
Capturing core dump and information to /var/opt/mssql/log...
Hint: You are currently not seeing messages from other users and the system.
      Users in groups 'adm', 'systemd-journal' can see all messages.
      Pass -q to turn off this notice.
No journal files were opened due to insufficient permissions.
Hint: You are currently not seeing messages from other users and the system.
      Users in groups 'adm', 'systemd-journal' can see all messages.
      Pass -q to turn off this notice.
No journal files were opened due to insufficient permissions.
/usr/bin/tail: cannot open '/var/log/syslog' for reading: Permission denied
Attempting to capture a dump with paldumper
Captured a dump with paldumper
Core dump and information are being compressed in the background. When
complete, they can be found in the following location:
  /var/opt/mssql/log/core.sqlservr.04_05_2019_15_01_09.11792.tbz2
Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
in /var/opt/mssql/log for more information.

The errorlog (relevant bits):

Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
        Mar 12 2019 19:29:19
        Copyright (C) 2017 Microsoft Corporation
        Express Edition (64-bit) on Linux (Ubuntu 18.10)
2019-04-05 14:58:38.41 Server      Failed to verify the Authenticode signature of 'C:binnsecforwarder.dll'. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support.
2019-04-05 14:58:38.44 spid9s      Polybase feature disabled.
2019-04-05 14:58:38.44 spid9s      Clearing tempdb database.
2019-04-05 14:58:38.52 spid18s     A self-generated certificate was successfully loaded for encryption.
2019-04-05 14:58:39.00 spid9s      Starting up database 'tempdb'.
2019-04-05 14:58:39.23 spid17s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2019-04-05 14:58:39.24 spid23s     The Service Broker endpoint is in disabled or stopped state.
2019-04-05 14:58:39.24 spid23s     The Database Mirroring endpoint is in disabled or stopped state.


Get this bounty!!!

#StackBounty: #sql-server #azure-sql-database #active-directory #azure Service Principal as SQL Active Directory Admin, does it use Gra…

Bounty: 100

I’m looking to use a service principal as the server admin, so it can be used in a release pipeline to create further active directory users.

I’m successfully able to make the service principal the server admin* and connect to the database using an Access token, so the service principal authentication works fine, which is great nice and an interesting challenge.

*EDIT: The AAD server admin was creating via the ARM template, notably if you tried creating manually via the portal, you cannot select service principals as admin users. But interestingly you can see the service principal name ok in the portal after creation using ARM instead. And you can login successfully with AAD auth, so it appears a server principal admin works fine.

However when creating further users, they never seem to be found with the error:

“Principal ‘name here’ could not be found at this time. Please try again later.”

However when logged in as myself I am able to create other users fine.

Which leads me to think is SQL Server using some sort of Active Directory impersonation to validate the subsequent users / service principals exist?

If I were to grant my service principal access to the Graph API would that give it enough permissions? If so what is the minimum rights necessary?

I also see that Azure SQL Server has created a managed service identity in the background, so I wonder if that’s also at play?

Also I saw on managed instances that they explicitly require a service principal with AAD rights to hook into AAD – which I presume is like an Azure SQL Server’s MSI? But Azure SQL Server doesn’t mention this with an AAD admin being enough.

I’d love to know how the underlying mechanism for the active directory hookup works as it could help me investigate my challenges further.


Get this bounty!!!

#StackBounty: #sql-server #oracle #reporting-services #ssrs-2008-r2 #ssrs-2017 Oracle Date format exception in SQL Server Reporting Ser…

Bounty: 100

Earlier my client was using SSRS 2008R2 with Oracle as transaction database. Recently they have upgraded to SSRS 2017 and now many reports are throwing following error:

ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
[AbnormalTermination:ReportProcessing],
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
An error has occurred during report processing. —>
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Query execution failed for dataset ‘Ds_Main’. —>
Oracle.ManagedDataAccess.Client.OracleException: ORA-01830: date
format picture ends before converting entire input string

After closely looking into report query, I have noticed that this error is for all those reports where oracle function TO_DATE(<Date Value>) has been used without date format. For example:

To_date(:Date_Parameter) -> this syntax throws above mentioned error
To_Date(:Date_Parameter,’MM/DD/YYYY’) -> this syntax works perfectly

Any suggestion to fix this issue without updating bunch of reports, please


Get this bounty!!!

#StackBounty: #sql-server #sql-server-2012 #sql-server-2014 #sql-server-2016 #extended-events Incorrect process utilization from System…

Bounty: 50

I have been recently working on understanding the data or metrics being stored in system health extended events event file.

We are trying to implement the data collection of performance metrics using system health as provided here

There are reports that gives metrics like CPU UTILIZATION, other process utilization etc being collected from system health event named

scheduler_monitor_system_health_ring_buffer_recorded

I am not able to understand for few of my busy servers why the field “process_utilization” which is listed in report as SQL CPU utilization be most of time above 100. IN peak time it varies between 120-160 and because of which the report is always showing CPU above 100 even though when i checked from servers activity monitor that’s never the case.

I’ve raised this issue in Github but seems like no fix or response.

Therefore, my question would be

  1. How can i get accurate number for SQL CPU utilization for my servers using system health ring buffer recorded?

  2. Report also shows counter for below 2 fields calculated per their report

  1. 100-System_idle-process_utilization as OtherProcessUtil

  2. 100-system_idle as SystemUtil

What are these OtherProcessUtil and SystemUtil needed/helpful for ?

  1. I also see memory utilization every time shows 100%. That does not seem correct as well. Has anyone noticed it?


Get this bounty!!!

#StackBounty: #python #sql-server #sqlalchemy SQLAlchemy and SQL Server Datetime field overflow

Bounty: 50

I’m using SQLAlchemy to connect to a SQL Server database.

I’m trying to insert an object into a table from my python script and it’s failing. I’m receiving the error:

(pyodbc.DataError) ('22008', '[22008] [Microsoft][ODBC SQL Server Driver]Datetime field overflow (0) (SQLExecDirectW)')

It looks like this is being caused by the following datetime object:

datetime.datetime(214, 7, 21, 0, 0)

… that’s the 21st July 214

The corresponding date time field in the SQL Server table is of type datetime2.

It looks like the conversion from python/SQLAlchemy to SQL Server isn’t adding a ‘0’ to beginning of the year value. I’ve confirmed this by the fact that I can manually add this date to the SQL Server using an INSERT statement with and without the leading ‘0’.

Is there a way to force the year part of the date into the correct format? Or is this being caused by something else?


Get this bounty!!!

#StackBounty: #sql-server #azure-sql-database SQL Server logging within Azure

Bounty: 50

I’m looking at an xel file that’s been exported from Microsoft Azure Storage Explorer.

One specific query:

select top 1 dist.Distance...

which should only “affect” one row. Without top 1, the query would never return more than 10 rows.

But the xel file reads that the affected_rows were 210720 for that query.

What does that number represent?

EDIT – Adding full query

declare @Latitude float = 0,@Longitude float = 0

select top 1 dist.Distance, f.id, f.SEOPageTitle, f.SEOMetroCity, f.SEOCity1, f.SEOCity2, f.SEOCity3, f.MetaDescription, f.MetaKeywords 
FROM f (NOLOCK) 
join (select distinct f.id, min(dbo.CalculateDistance(@Latitude, @Longitude, z.Latitude, z.Longitude)) 'Distance' 
        FROM f2 (NOLOCK) 
        join FZipCode fz (NOLOCK) on f2.id = fz.FID 
        join (select distinct ZipCode, Latitude, Longitude from ZipCodes (NOLOCK)) z on z.ZIPCode = fz.ZIPCode 
        where f.Activated = 1 and dbo.CalculateDistance(@Latitude, @Longitude, z.Latitude, z.Longitude) <= 30 
        group by f2.id) dist on f.id = dist.FID 
order by Distance


Get this bounty!!!

#StackBounty: #sql-server #tsql #geometry #spatial #sql-server-2017 Preserve start points in UnionAggregate

Bounty: 100

DECLARE @Geom TABLE 
( 
   shape geometry, 
   shapeType nvarchar(50) 
); 

INSERT INTO @Geom(shape,shapeType) 
VALUES('LINESTRING(1 2, 3 4)', 'A'), 
('LINESTRING(3.2 4, 7 8)', 'B'); 

SELECT *
FROM @Geom

SELECT geometry::UnionAggregate(shape).ToString(), geometry::UnionAggregate(shape)
FROM @Geom;

The WKT for the output is

MULTILINESTRING ((7 8, 3.2 4), (3 4, 1 2))

when I would want

MULTILINESTRING ((1 2, 3 4), (3.2 4, 7 8))

Where the beginning of the “A” and “B” line should be (1 2) and (3.2 4) respectfully.

This behavior of UnionAggregate doesn’t seem to care about “direction” of the geometry in order to maintain that A union B and B union A is the same result. However, I want to preserve start/endpoints as I am unioning street geometry and I want all the LINESTRINGs to go in their original direction.

This problem is discussed here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/89e95366-3649-4294-a0bc-f3921598157f/union-of-linestrings-and-reversing-direction?forum=sqlspatial

They seem to suggest at a possible solution about checking the end result, but it is not clear to me how to do that. It is hinted at in a linked thread that

The MultiLineString always represents the graph from the point which farthest from the origin point.

It is not clear to me exactly what this means, but I don’t think I can just assume the result of a UnionAggregate is always the reverse of what I want

If it is hard to know directional intent then I can add M measures where the direction should follow increasing M values.

Assuming I have a method for reversing the points in line, how would I go about solving for this?

I found a function that mimics for STUnion for added support for Z and M measure: http://www.spatialdbadvisor.com/files/SQLServer.html#robo48 however it is noted that “their direction could change (eg Start/Start Point relationship).”, which is what I want to avoid.


Get this bounty!!!