Feeds:
Posts
Comments

Archive for the ‘Security’ Category

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Quick  one today:

With regular frequency, there is a need sometimes to change the password of a login. Using SSMS, its an easy step. But when we have a lot of such changes, its gets easier to use T-SQL script.

--
-- Change Login Password
--
ALTER LOGIN LoginName
WITH PASSWORD = 'NewPassword'
GO

For more  info

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

We have a bunch of lab Sql Server boxes machines and sometimes after a fresh Sql Server install, when we try to open Activity Monitor, we run into this problem.

Error:

 

TITLE: Microsoft SQL Server Management Studio
 ------------------------------

The Activity Monitor is unable to execute queries against server DC2POLTPS02.
 Activity Monitor for this instance will be placed into a paused state.
 Use the context menu in the overview pane to resume the Activity Monitor.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)

------------------------------

Since these are lab machines, we are remotely logged into the machines and looks like there is some setting that prevents Activity Monitor from opening successfully. Activity Monitor provides great detail on what is going on with Sql Server at any given point-in-time and such activity needs “high level insight” into the Operating System and Sql Server; Such “high level” permissions are not enabled by default for user accounts.

Following steps show a way to enable elevated permissions when logged in remotely.  From what I could gather from Microsoft Connect this seems like elevated permissions on remote operating system’s DCOM. So we need to enable Remote Launch & Remote Activation permissions on remote Operating System (lab machine)

Resolution:

RDP to the remote machine and

  1. Open Component Services (DCOMCNFG) from start menu
  2. In the left hand tree, under Console Root, expand Component Services, expand Computers, right-click on My Computer and go to Properties
  3. In My computer Properties window, go to COM Security tab.
  4. In the Launch and Activation Permissions section, click on Edit Limits button.
    1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    2. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
  5. In the Access Permissions section, click on Edit Limits button
    1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    2. Once user is added, highlight the user and make sure it has Remote Access permissions checked.
  6. Hit Okay to save changes.
  7. Now expand the My Computer in the left-hand tree and go to DCOM Config.
    1. Find Windows Management and Instrumentation and go to Properties.
    2. Go to Security tab and under Launch and Activation Permissions section, click on Edit button
    3. In the Security tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    4. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
    5. (See the image below)
  8. Save all changes and re-open Activity Monitor
Activity Monitor Error

Activity Monitor Error

 

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

A few days ago, this error appeared on one of our lab machines.

Error Message:

Msg 15281, Level 16, State 1, Line 16
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 
'Ad Hoc Distributed Queries' because this component is turned off as part of 
the security configuration for this server. A system administrator can enable 
the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information 
about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries'
in SQL Server Books Online.

Resolution:

This is an easy error to fix — as the error message is pretty verbose and self-explanatory. After making sure that ad hoc distributed queries are allowed (acceptable to be executed) in your sql environment, run the following query to enable execution of ad hoc in your Sql instance.

--
-- Check current status of Ad Hoc Distributed Queries
--
SELECT * FROM sys.configurations WHERE name LIKE '%ad hoc Dis%'
GO

--
-- Enable Ad Hoc Distributed Queries
--
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server allows assigning or GRANTing different applicable permissions to different objects. Since there are so many nuances, sometimes it gets confusing. So this cheat sheet helps reduce the confusion and always come in handy when assigning permissions to securables. Some are listed below:

  • Tables
  • View
  • Type
  • Stored Procedure
  • Functions
  • Synonym
  • User
  • Role
  • Schema
  • Database
  • Login
  • Server

Tables:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to tables
--
GRANT SELECT                     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT ALL                        ON OBJECT::dbo.StoredValue    TO SomeUser
GO

Views:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT SELECT                   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT ALL                      ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GO

Type:

--
-- Grant permissions to views
--
GRANT VIEW DEFINITION          ON TYPE::dbo.SSN                TO SomeUser
GO

 

Stored Procedures:

  • ALL does not mean all possible permissions. For stored procedures it only means EXECUTE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT EXECUTE                 ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GRANT ALL                     ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GO

Functions:

  • Assigning ALL permissions only means the following for functions
    • Scalar function permissions: EXECUTE, REFERENCES.
    • Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
--
-- Grant permissions on functions
--
GRANT EXECUTE                 ON OBJECT::dbo.udf_WhatIsMyAge              TO SomeUser
GRANT ALL                     ON OBJECT::dbo.udf_CalculateAge             TO SomeUser
GO

 

Synonym:

  • When dealing with Synonyms we still need to follow the same ownership-chain. Along with permissions to use synonym, we also need permissions base objects.
--
-- Grant permissions on Synonyms
--
GRANT EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
DENY  EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
GO

 

User:

  • A user is a database-level securable contained by the database in which it exists.
--
-- Grant permissions on Users
--
GRANT CONTROL                 ON USER::SomeUser1                          TO OtherUser
GRANT IMPERSONATE             ON USER::SomeUser1                          TO OtherUser
&amp;amp;lt;/pre&amp;amp;gt;&amp;amp;lt;pre&amp;amp;gt;GO 

Role:

  • Just like user, a Role is also a database-level securable, in which it exists.
--
-- Grant permissions on Role
--
GRANT ALTER                   ON ROLE::r_ExecuteReportsOnWeekdays         TO SomeUser
GO

 

Schema:

  • A schema is also a database-level securable, in which it exists.
--
-- Grant permissions on schema
--
GRANT SELECT                  ON SCHEMA::HumanResource                    TO SomeUser
GRANT INSERT                  ON SCHEMA::ArchiveObjects                   TO SomeUser
GO

 

Database:

  • A database is a instance-level securable, in which it exists.
  • Several permissions could be assigned on a database; When we mention ALL, the following specific permissions are assigned implicitly:
    • BACKUP DATABASE
    • BACKUP LOG
    • CREATE DATABASE
    • CREATE DEFAULT
    • CREATE FUNCTION
    • CREATE PROCEDURE
    • CREATE RULE
    • CREATE TABLE
    • CREATE VIEW
    • SHOWPLAN
--
-- Grant permissions on database
--
USE [SomeDatabase]
GO
GRANT CREATE TABLE       TO SomeUser
GO

--
-- Grant permissions on see execution plan to a login
--
USE [TestDB]
GO
GRANT SNOWPLAN ON DATABASE::TestDB     TO [SomeUser]
GO

 

Login:

  • Logins are also instance-level securables.
--
-- Grant permissions on Logins
--
GRANT IMPERSONATE             ON LOGIN::ReportsLogin                      TO AdhocReports_Login
GO

Server:

  • Permissions at server scope could only be assigned when you are pointed to master database.
--
-- Grant permissions on server level objects
--
GRANT ALTER ANY DATABASE      TO DBA_Group
GRANT SERVER CONTROL          TO DBA_Group
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »