SQL Server Agent Roles

Background

We are evaluating using BMC’s Control-M, our corporate IT Job Management tool.

And, wanted to see what we will have to do to get it to work against SQL Server Instances.

 

SQL Server Agent Roles

In SQL Server, jobs are managed through SQL Server Agent.

PreDefined Roles

SQL Server Agent has predefined security roles.

SQL Server Management Studio ( SSMS )

Jobs are saved in the system database, msdb.

To view the roles, please do the following:

  1. Launch SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Instance
    • Choose System Databases
    • From the list of System Databases, choose msdb
    • Within the msdb database, transverse to Security \ Roles \ Database Roles
    • The SQL Server Agent roles are noted having names that start with SQLAgent

       

 

Permission Set

Let us dig deeper into these roles and see what they afford us, where they are different, and what is the minimum we can get away with.

Concentric

The roles are listed in increasing order of privileges assigned.

More precisely as Microsoft would say it, they are concentric.

Looked up the term concentric and here is how it is defined:

They are of or denoting circles, arcs, or other shapes that share the same center, the larger often completely surrounding the smaller.

And, so we can see that MSFT’s documentation is very useful, and here it is in verbatim:

Link
The SQL Server Agent database role permissions are concentric in relation to one another — more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 

Tabulated

Role Definition Details
SQLAgentUserRole Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.  a) Have permission on owned jobs
SQLAgentReaderRole SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. a) List all jobs – their properties, schedules, and executionhistory
SQLAgentOperatorRole SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. a) Manage ( enable or disable jobs, edit job steps )

b) They can execute, stop, or start jobs

c) Delete job execution history

 

Which Role?

For system jobs we do not want an external job manager as SQL Server Agent is able to do so sufficiently.

We only want an external job manager for specific jobs.

Let us review the predefined system roles and judge their appropriateness for what we have in mind:

  1. SQLAgentOperatorRole
    • Affords all roles to all jobs
    • Too much for us
  2. SQLAgentReaderRole
    • (+)
      • Able to create and and manage own job
      • Read privileges on all jobs; their steps, schedule, and run history
    • (-)
      • Job Management does not need to view job data nor review job run history
        • A bit much for our targeted need
  3. SQLAgentUserRole
    • Requirements
      • (+)
        • Create own jobs
        • Run owned jobs
          • Existing jobs ownership can be re-assigned
      • (-)
        • Job has be owned
          • We have to review what is the ramification of changing job ownership for each specific job

 

Follow Up

Our follow-up task is to review the impact of changing job ownership for specific jobs.

 

References

  1. Microsoft
    • SQL Server Agent Fixed Database Roles
      Link
    • Implement SQL Server Agent Security
      Link

 

General Vincent K. Brooks

Background

In an age where voices can be reduced to soundbites, cliques and inflammatory comments and categorization.

In general finding the worst in each other and blanket statements about “what we have seen before“.

And, those buckets are based on National Origin and Religion.

How do we go forward?

And, so we ask ourselves how do we go forward or are we are just in a maze of bad choices, which leads to stillness, and assumption of a fetal position.

As I was watching an interview yesterday, I heard the name of Vincent Brooks.  I googled on his name and found a couple of freely and broadly accessible videos on youtube.

 

Video

  1. General Brooks discusses his biggest challenges and biggest successes in Iraq.
    Uploaded On :- 2011-May-4th
    Link
  2. GEN Brooks message
    US Pacific, 4 Star General
    Uploaded On :- 2013-July-22nd
    Link
  3. LTG Brooks West Point Visit.mov
    Lt. Gen. Vincent K. Brooks, Commander of 3rd Army, returns to his Alma Mater to speak to the Corps of Cadets about Army Leadership.
    Uploaded On :- 2012-April-10th

    Link

 

Indepth

GEN Brooks message, US Asia Pacific

  1. Command Video for Team 6
  2. Team Qualities
    • True test of a team is not missing a beat even as we change command
  3. Truly blessed to return the Four Star general to Asia Pacific since 1974
  4. Media
    • Another channel for me to air directly
    • It is not substitute to see and hear in person
  5. Opportunity
    • Training
      • Training our own and our partners and friends
    • Professionalism
      • Exporting professionalism
      • Your Professionalism will be available to our partners
        • Qualities
          • Be yourself
          • And, give each task your best effort
  6. Challenges
    • Fiscal Challenges
      • Fiscal challenges we have has a nation
        • Every dollar we are given, we have to stretch
        • Take care of our people and realize that we are fortunate to have the ones we have
    • Changing Culture
      • We can not allow practices that undermine our pride and the pride we feel as an Army
      • Eliminate Sexual Harassment and Sexual Assault
        • Actions that leave trauma in unit and members of our team
        • Have a culture where this experiences are not able to occur
        • To do
          • Start with yourself
          • Allow others to make it go away
          • Set example for others to see
        • zero tolerance
        • Trust
    • Gratitude
      • Thanks for welcoming my wife and I

 

Webprofusion Ltd – Certify The Web – Day 1

Background

Security is being in the news a lot lately.

In this post, we will talk about using SSL, specifically reaping SSL certificates from LetsEncrypt.Org via “WebProfusion Ltd – Certify the Web“.

LetsEncrypt.Org

Client Options

Here are the Client Options available for Windows

Link

WebProfusion Ltd – Certify GUI –
(.Net, WinForms )

In this post, we will go with WebProfusion Ltd – Certify the Web.

 

Requirement

Outline

  1. Network
    • DNS
  2. Website Availability
    • Website Availability Test
  3.  Software
    • Microsoft .Net v4.5
  4. Microsoft IIS
    • Bindings

Network

DNS

DNS Requirement

From a networking standpoint, the LetsEncrypt validation servers have to able to connect to the originating computer.

That rules out the following:

  1. Servers that are not reachable over the Internet
    • Servers that only have local IP Addresses

 

DNS Server Names

Here are a couple of popular DNS Servers:

Vendor Link DNS-1 DNS-2
Verisign  Link  64.6.64.6  64.6.65.6
Google  Link  8.8.8.8  8.8.4.4
OpenDNS  Link  208.67.222.222  208.67.220.220

 

DNS Validation
nslookup

On MS Windows, we can use nslookup to validate.

Syntax

Here is the syntax


nslookup [FQDN] [dns-server]

Sample – DNS – Google ( 8.8.4.4 & 8.8.8.8 )
Code

nslookup web.labDomain.org 8.8.8.8 

Output

Sample – Verisign ( 64.6.64.6 & 64.6.65.6 )
Code

nslookup web.labDomain.org 64.6.64.6 

Output

 

Website Availability

Website Availability Test

Here are some availability tools:

  1. Uptrends

 

Uptrends.com

Go to https://www.uptrends.com/tools/uptime.

Intentionally entered an invalid URL, in this case upTimeTest.cnn.com

Uptrends.com – Sample

uptimeTest.cnn.com

We entered a FQDN that we know is not available.

hyattHouse.com

We entered hyattHouse.com and we are able to successfully validate.

 

Software

Microsoft .Net Framework v4.5

Although the software can be installed without first installing .Net v4.5, it can not be used.

If one tries to do so, the user is prompted to install .Net 4.5.

BTW, .Net v4.5 has its own requirement in terms of minimal OS.  And, those are:

  1. Windows 2003
    • .Net v4.5 can not be installed on MS Windows 2003
  2. Windows 7
  3. Windows 2012

 

Microsoft IIS

IIS – Site Bindings

Internet Information Server ( IIS )

Site Bindings

We can use IIS Manager and access the Site Bindings

Site Bindings – Original

 

Site Bindings – Add Binding

Click on the “Add..” button.

Add each hostname or alias that you will like to generate certificate for.

Please add only http entries.

The https will be added for you.

 

Site Bindings – After adding
  
Explanation

In the screen above, we have added the hostname that we will like exposed.

 

Download

Downloaded “Certify The Web” from the Vendor’s website.

As of 2017-July-15th, the current version is V2.0.7-beta4.

Installation

ScreenShots

License Agreement

Image

 

Select Destination Location

Image

Explanation

  1. 9 MB

 

Select Start Menu Folder

Image

 

Ready to Install

Image

Installing ….

Image

Complete the Wizard

Image

 

Usage

Launch “Certify the web“.

Initial Screen

Empty Canvas

New Certificate

Click the “New Certificate” button.

Managed Sites – New Certificate – Options

Image

Explanation

  1. Select IIS Site
    • Chose the IIS Site
  2. Name
    • The Name is only figurative
  3. Primary Domain Name
    • Please choose the Domain Name
    • If none shown, please visit the TroubleShooting section
  4. Alternative Domain Subject Name
    • All of the hostnames registered in the Site Bindings are listed

 

Managed Sites – New Certificate – Advanced

 

 

Explanation

  1. Auto create/update IIS bindings ( use SNI )
    • Chose to use SNI
      • Please read more about SNI ( Server Name Indication )
      • As always Wikipedia is a good source and here is the Link

 

Once you are comfortable with your choices, please click the Save button.

 

Request Certificate

Here are the steps for actually requesting a certificate.

Saved Certificate Request

Here is the screen once a Certificate is Requested.

Image

 

Certificate Received and Installed

Image

Explanation

  1. In the image above, our request has been validated, a certificate has been issued, and installed on our machine.

 

Troubleshooting

Primary Domain Name

Primary Domain Name – Empty

In the example that follows we just installed the Application and we are trying to add a “New Certificate”.

New Certificate

Error – “A primary domain must be selected”

Explanation:

  1. The error message states “A Primary Domain” must be selected
    • The reason is because we have not selected “Primary Domain Name

 

Remediate:

To fix, please …

  1. Launch IIS Manager
  2. Access Site
  3. Under Sites, select the Web Site
  4. In the Action Panel
    • Under Edit Site, Choose Bindings…
  5. In the “Site Bindings” window
    • Review listed Site Bindings
    • If not listed, click the “Add” button
      • The “Site Binding” window appears
        • In the Host name text box, add the host’s “Fully Qualified Domain Name

 

Summary

If you are running at a minimum MS Windows 7 ( desktop)  or 2012 ( server ), you should consider “Certify The Web“.

There is a lot more as this is only Day ONE.

 

References

  1. Certify The Web
    • Home Page
      Link
    • Docs
      Link
    • Getting Started
      Link
    • Issues
      • Issues – does not give list of possible domains #83
        Link
  2. Server Name Indication
  3. Browser – SSL
    • Google Chrome
      • Akemi Iwaya
        • Akemi Iwaya – How Do You View SSL Certificate Details in Google Chrome?
          Link
  4. DNS Servers – Public
    • Lifewire
      • LifeWire – Free & Public DNS Servers
        Link

Windows – Reviewing Services Start and Stop Times via Event Viewer & Log Parser Studio

Background

Received an Incident this morning stating that out Power BI Gateway Service was done.

Spent a bit of time trying to see what brought the service down.

Code


/*  
   Service Start and Stop Time
*/
SELECT 
             TimeGenerated
           , TO_STRING(TimeGenerated, 'yyyy-MMM-dd hh:mm tt') as [Timestamp] 
           , Message as [MessageLogged]
           , Strings as [StringsRaw] 
           , SUBSTR(Strings, 0, LAST_INDEX_OF(Strings,'|'))  as [ServiceName] 
           , SUBSTR(Strings, ADD(LAST_INDEX_OF(Strings,'|'), 1) , Strlen(Strings)) as [ServiceStatus] 

FROM '[LOGFILEPATH]'

where
     (
          ( EventID = 7036 )
          and 
          (
                 ( Message like '%SQL%' )
              or ( Message like '%gateway%' )


          )
  
     )

ORDER BY
      TimeGenerated desc


Output

SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”

Background

In the process of installing “SQL Server 2016” for a new client.

As it is a new client and not really sure of which exact features they will be using, chose to install “All features“.

Install Blocked

Installation is blocked with the error pasted below:

ScreenShot

Feature Rules

Image

Text

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase.

Rule Check Result

Image

Text

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed.  The Oracle Java SE runtime environment is software provided by a third-party software.

To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

 

 

Remediation

Visited Microsoft URL

Visited the listed Microsoft page and directed right away to Oracle’s Java download page.

 

Java Standard Edition Downloads

Here are the downloads available for java Standard Edition ( Java SE)

 

Explanation

  1. JDK
    • We need we need JRE ( Java Runtime Engine), and not JDK ( Java Development Kit )
  2. Server JRE
    • Thought we could get away with the the lighter footprint Server JRE
  3. JRE
    • JRE

 

Server JRE

Server JRE – Downloads

Chose Windows x64.

Install Package Contents

The installer is bundled as a tar.gz.

Using 7-Zip extracted the file, but no installer.

Just app executable and class files.

Need more than that to install JRE and have SQL recognize that Java JRE is installed.

 

JRE

Java SE Runtime Environment 8 Downloads

Needed an executable offline installer and our bitness is 64 bit and so chose jre-8u131-windows-x64.exe.

 

Install

Actual Installation is straightforward.

ScreenShot

Welcome

 

Destination Folder

Create destination Folder beforehand.

Here is a sample script.


@echo off
rem _folder=C:\Program Files\java\jre1.8.0_131
set "_folder=E:\ProgramFiles\Java\JRE1.8.0_131"

if not exist %_folder% mkdir %_folder%


 

 

 

Destination Folder ( Changed )

 

Java Setup – Progress

 

Java Setup – Complete

Summary

If you will be evaluating \ using Polybase as part of SQL Server v2016, please download and install Oracle JRE ( Java Runtime engine) beforehand.

For JRE, there are two packages available for download.  One is marked Server and the generic one.

Please choose the generic, full version.

SQL Server Agent Jobs and extraneous contents in logged messages

Background

This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.

 

Message

Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.

 

Explanation

  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)

 

Q/A

Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?

Link

 

Troubleshooting

SQL Profiler

Ran SQL Server Profiler and here is a screenshot:

Image

 

Outline

  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
    • SET TEXTSIZE 1024
    • print ‘hello’

Detail

Hello

Hello

  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636

 

Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe

Remediate

SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration

Changes

  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b

Explanation

We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.

 

Recap

Logged Messages

Logged Messages – Original

Logged Messages – Revised

 

Dedicated

Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.