Microsoft

SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Daniel Adeniji -2147467259, 229, Microsoft, MS SQL Server, SQL Server Agent, SQL Server Profiler, SQLCMD, Technical

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259. “
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server – Database Diagram – Metadata

Daniel Adeniji Database Diagram, Microsoft, MS SQL Server, Technical ,

Background

Wanted to quickly document the database objects that are provisioned when the user requests “Database Diagramming” on a database.

Please keep in mind that this is not an instance level operation.

It targets the context database.

 

List

Object Type Object Usage Code
Function
Function dbo.fn_diagramobjects Counts number of system supplied database diagram helper objects currently available SELECT [kb].dbo.fn_diagramobjects()
Stored Procedure
dbo.sp_alterdiagram Alter existing diagram update dbo.sysdiagrams ( data passed in via SP )
dbo.sp_creatediagram Create new diagram inserts into dbo.sysdiagrams ( data passed in via SP )
dbo.sp_dropdiagram Drop existing diagram  delete from dbo.sysdiagrams ( data passed in via SP )
dbo.sp_helpdiagramdefinition Return diagram’s definition select version, definition FROM dbo.sysdiagrams ( diagram_id passed in via SP )
dbo.sp_helpdiagrams Return diagram information SELECT *
FROM sysdiagrams
WHERE ( @dboLogin = 1 OR USER_NAME(principal_id) = @user)
AND ( @diagramname IS NULL OR name = @diagramname)
AND ( @owner_id IS NULL OR principal_id = @owner_id)
dbo.sp_renamediagram Rename diagram
dbo.sp_upgraddiagrams Copies diagram data from dbo.[dtproperties] into dbo.sysdiagrams
Table
dbo.sysdiagrams Contains metadata on user created diagrams

Tables

dbo.sysdiagrams

Indepth

SQL Server Profiler

Scenario

New database Diagram Requested

When a new Database Diagram is requested…

 

Database Diagram Preparation

When a database is being prepared for Database Diagram.

Summary

It appears that diagram metadata were previously stored in  dbo.dtproperties.

But, they are now stored in dbo.sysdiagrams.

SQL Server – Database Diagram Permissions – “Day to Day”

Daniel Adeniji Database Diagram, Microsoft, MS SQL Server, Technical

Background

In an earlier post, SQL Server – Database Diagram Permissions ( Link ), spoke about veeting the need to grant db_owner privilege to allow users to diagram databases.

Exercise

Create Login & Grant Access to DB

Create a new login and granted that login access to the targeted database.

In our case, our target db is WideWorldImportersDW.

User without any object level access

Here is what is available to a user who has not been assigned object level permissions.

 

 

Grant user access to targeted objects

Script


USE [WideWorldImportersDW]
GO

grant select on [Fact].[Sale] to [ls]
go

grant select on [Dimension].[City] to [ls]
grant select on [Dimension].[Customer] to [ls]
grant select on [Dimension].[Date] to [ls]
grant select on [Dimension].[Employee] to [ls]
grant select on [Dimension].[Stock Item]  to [ls]
go

Diagram Objects – Post User granted object level access

Here is the view once user has been granted object level access.

Diagrammed Objects

Here is our objects diagrammed.

Summary

Proved that once a database is configured with database diagramming objects, users are able to diagram any objects they have been assigned read permissions.

 

SQL Server – Database Diagram Permissions

Daniel Adeniji Database Diagram, Microsoft, MS SQL Server, Technical , , ,

Request

Last Friday I received a new request.

Here is the request:

Image

Textual

Hi Daniel,

I created this ticket to get “DB owner” access for the REDWOOD* DBs, I need this access so I can create DB diagrams.
Let me know if you need any additional info.

Docs

Googled to make sure db_owner privileges is indeed needed.

And, confirmed it is needed and so went ahead and granted it.

Listed below is the referenced documentation.

Understanding Database Diagram Ownership (Visual Database Tools)

Here is the referenced work.

Image

Textual

  1. To use Database Diagram Designer it must first be set up by a member of the db_owner role (a role of Microsoft SQL Server databases) to control access to diagrams. Each diagram has one and only one owner, the user who created it.
  2. Although any user with access to a database can create a diagram, once the diagram has been created, the only users who can see it are the diagram’s creator and any member of the db_owner role.
  3. Ownership of diagrams can only be transferred to members of the db_owner role. This is only possible if the previous owner of the diagram has been removed from the database.
    If the owner of a diagram has been removed from the database, the diagram will remain in the database until a member of the db_owner role attempts to open it. At that point the db_owner member can choose to take over ownership of the diagram.

Scenario

Good weekend.

And, with a rested mind wanted to review the steps the user followed and how the system responds without db_owner permission.

 

New Database Diagram

Request to create database diagram

Is NOT db_owner

Here is what happens when the user is not a db_owner.

SSMS

Image

Textual

A member of the db_owner role must use the database diagramming functionality in order to setup the required database diagramming objects on the SQL Server.

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner

 

Is db_owner

Here is what happens when the user is a db_owner.

Image

Textual

This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?

 

SQL Server Profiler

Image

Textual
  1. SELECT [DBLAB].dbo.fn_diagramobjects()
    • Error :- Cannot find either column “DBLAB” or the user-defined function or aggregate “DBLAB.dbo.fn_diagramobjects”, or the name is ambiguous.
  2. exec sp_executesql N’SELECT dtb.compatibility_level AS [CompatibilityLevel],
    dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb WHERE
    (dtb.name=@_msparam_0)’,N’@_msparam_0 nvarchar(4000)’,@_msparam_0=N’DBLAB’
  3. SELECT CONVERT(bit,IS_MEMBER(‘db_owner’))
  4. execute as user = N’dbo’;
    revert;
Explanation
  1. Issue query against dbo.fn_diagramobjects scalar function
  2. Determine database’s compatibility level
  3. If current user is member of db_owner
  4. Execute as dbo
  5. Revert

Objects Created

Once logged in as sysadmin opted to create diagramming objects, here are the objects created and permissions granted.

Details

  1. Create Database Objects
    • Create Stored Procedure ( SP )
      • dbo.sp_upgraddiagrams
      • dbo.sp_helpdiagrams
      • dbo.sp_helpdiagramdefinition
      • dbo.sp_creatediagram
      • dbo.sp_renamediagram
      • dbo.sp_alterdiagram
      • dbo.sp_dropdiagram
    • Create Function
      • dbo.fn_diagramobjects
    • Create Table
      • dbo.sysdiagrams
      • dbo.sysdiagram_properties
  2. Add Extended Property
    • For each object created, please add extended property
  3. Clean Up tasks documented
    • Though obviously not acted upon, the objects that should be removed to rollback the changes are listed

Tabulated

Here is a tabulated view of the objects created.

Object Type Object Name Permission
Procedure
dbo.sp_upgraddiagrams
dbo.sp_helpdiagrams Grant execute to public;
Deny execute to guest;
dbo.sp_helpdiagramdefinition Grant execute to public;
Deny execute to guest;
dbo.sp_creatediagram Grant execute to public;
Deny execute to guest;
dbo.sp_renamediagram Grant execute to public;
Deny execute to guest;
dbo.sp_alterdiagram Grant execute to public;
Deny execute to guest;
dbo.sp_dropdiagram Grant execute to public;
Deny execute to guest;
Function
dbo.fn_diagramobjects Grant execute to public;
Deny execute to guest;
Table
dbo.sysdiagrams
dbo.sysdiagram_properties

 

Summary

Here I am granting db_owner permission to a user so she can diagram databases.

Even worse Opened up a Connect Item.

Thanks goodness for a long weekend.

Wish I could blame it on the smoke, but then things cleared up on Sunday.

All the faults on a lazy Friday.

Could have and should have waited on a Monday and confirmed that once the dba provisioned the targeted database, db_owner is likely not needed.

SQL Server – Upgrade – “It is not possible to change the SQL Server features to be upgraded in this release”

Daniel Adeniji Installation, Microsoft, MS SQL Server, Service pack Upgrade, Technical ,

Background

Trying to upgrade the SQL Server Instance, but kept shooting blanks.

 

Blanks

SQL Server v2005 to v2014

Upgrade to SQL Server 2014

Shared Features

Textual

It is not possible to change the SQL Server features to be upgrade in this release.

Image

Select Features

Textual

There are validation errors on this page.  Click OK to close this dialog box. Review errors at the bottom of the error page, then provide valid parameters or click Help for information.

There are no features selected for upgrade.

TroubleShooting

Looked everywhere on this one.

But, then thought back to a few months past.

 

SQL Server Instance

Version Info

SQL


select @@version

Output

Image

Textual

Microsoft SQL Server 2005 – 9.00.5000.00 ( Intel X86 )
Dec 10 2010 10:56:29
Express Edition on Windows NT 6.1 ( Build 7601: Service Pack 1)

 

Tabulate

  1. Version :-
    • We are running Microsoft SQL Server v2005
  2. Platform :- Intel x86
  3. Edition :- Express Edition
  4. OS :- Windows NT 6.1 ( Build 7601 : Service Pack 1 )
    • MS Windows 2008/R2
    • Service Pack 1

 

32 Bit?

It appears that we are running 32-bit SQL Server

Control Panel

Services
Image

Textual
  1. path to executable :- C:\Program Files (x86) \XDM-SQLExpress\MSSQL.1\MSSQL\Binn\sqlservr

 

Explorer

Folder :- C:\Program files (x86) \ XDM-SQLExpress – MSSQL.1 \ MSSQL \ Data
Image

Explanation

Confirmed that our files are in the “C:\Program Files x86” folder

Remediation

  1. Prepare to re-Install
    • Preserve SQL Server data and log files
      • Take SQL Server Services offline and copy files over to safe location
      • Backup databases
    • Capture SQL Server Logins
  2. Uninstall Microsoft SQL Server v2005
  3. Install Microsoft SQL Server v2014
    • Our OS, MS Windows 2008-R2, only let us go up v2014
    • Apply MS SQL Server v2014 – SP2 and Cumulative Updates
  4. Restore
    • Re-create SQL Server Logins
    • Re-attach data & log files

Summary

Sometimes simple SQL Server Upgrades take on a life of their own.

Office365 – Email Connectivity Testing through Mozilla Thunderbird

Daniel Adeniji Domain Name Server (DNS), Email, Mail, Microsoft, Microsoft Network Monitor, Mozilla.Org, Office 365, Reporting Services, Technical, Thunderbird

Background

Earlier this weekend got an email that analysts were no longer receiving emails that we had subscribed them to via Microsoft Reporting Services.

Troubleshooting

Log Files

Checked the various log files ( Reporting Services, IIS SMTP).

BTW, we are using IIS SMTP as a Smarthost Relay.

The logs were not illuminating.

 

Mozilla Thunderbird

Tried the same configuration with slight variations.  Actually many more times than I can remember.  And, definitely much more than I will admit.

 

Google

That chick named Google is your friend and so looked for how to configure email clients to use Office 365.

Found some good leads, but nothing worked out.

Configuration

Process

Here is the process we undertook to configure Mozilla Thunderbird to use Microsoft Office 365.

  1. Launch Mozilla Thunderbird
  2. Choose the Account
    • Right click on the Account you have selected
    • And, from the dropdown menu, choose “Settings“
  3. Account Settings
    • The “Account Settings” window appears
    • At the bottom of the “Account Settings” window we will observe the “Account Actions” panel
    • One of the choices available in the “Account Actions” panel is the “Add Mail Account” option
    • Please choose the “Add Mail Account” option
  4. “Mail Account” Setup
    • The “Mail Account Setup” window appears
    • Initial Screen
      • It is prefilled with your current system’s full name
      • Please enter your full email address
      • And, password
      • Once entered, please press the Continue button
    • Suggested Configuration
      • Based on the email address entered, Mozilla retrieves the domain name
      • The registered DNS Provider for the domain name is contacted
      • And, asked for MX record
      • Using the MX record, the servers registered for Mail are then contacted
      • If the Mail providers are able to provide mail registration data, communication is started with them
    • Manual Configuration
      • Please click on the manual config button to review or adjust configuration data

Images

Mail Account Setup
Access Account Configuration

Account Settings

Account Settings

Mail Account Setup – 01

Mail Account Setup – 02

Mail Account Setup – Looking up Configuration – Email Provider

Mail Account Setup – Looking up Configuration – Email Provider – Configuration found in Mozilla ISP database

Mail Account Setup – Manual Configuration

Configuration Results

Flow Protocol Server hostname Port SSL Authentication
Incoming IMAP imap-mail.outlook.com 993 SSL/TLS Normal password
Outgoing SMTP smtp-mail.outlook.com 587 STARTTLS Normal password

 

Network Monitoring

Microsoft Network Monitor

Once we were satisfied that we were able to establish communication between our host and the Mail Server, we used Microsoft Network Monitor to review the traffic.

Here is the filters we employed and what we captured via “Microsoft Network Monitor“.

Filter

Textual

ProcessName.Contains(“thunder”)

Image

Network Conversations

 

References

  1. Microsoft
    • Technet
      • Network Monitor Conversation Filtering
        Link

Summary

A good solution was so far away until we discovered that Mozilla Thunderbird is able to communicate with the Provider and request configuration data.