Advertisements

Archive

Posts Tagged ‘Security’

rReplication Configuration Issue

November 20, 2011 1 comment

Client Question

Replication setup keeps failing, permissions are correct.  Why does it keep giving me error code 5 (access denied).

Error Description

Command attempted:

\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\WOI_WorkOrde180608a5_10125.pre
(Transaction sequence number: 0x0000005F0001433C00CC00000000, Command ID: 10128)

Error messages:

The process could not read file ‘\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\20111114154553\WOI_WorkOrde180608a5_10125.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022

Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5

The error message is fairly clear; OS Error 5 is error messages returned from Windows API when it is trying to read from the file.  But why was he getting this error?  All permissions are set; SQL Server (Publisher) is able to write to it, why wouldn’t the SQL Server (Subscriber) be able to read it if it is running under same service account?

@SQLCAN: Replication Configuration Issue - Setup Screen

Replication Configuration Issue – Setup Screen

If you take note of the image the location the file is written to; its \\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\..; problem with this is the R$ share.  This is an administrative share, you must grant the SQL Server service account local administrative permissions on your Distributor to be able to read the file from there; which is against security best-practices.  Therefore a better way to do is to use UNC paths.

  1. Create a new folder on you Distribution Server.
  2. Grant your SQL Server Service Account(s), if you are using more than one, permissions to read/write to the folder.
  3. Create a HIDDEN share on the folder (any share with $ after words is hidden share; I generally think anything that doesn’t need to be public on network; hide it).
  4. Control the permission on the share also; so only SQL Server Service Account can read/write to it.
  5. Re-setup your Replication using \\ServerName\ShareName$\.

Client confirmed it worked for them 🙂

Advertisements

CREATE FILE encountered operating system error 5(Access is denied.)

April 23, 2010 5 comments

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

——————————
BUTTONS:

OK
——————————

If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

April 23, 2010 2 comments

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

——————————
BUTTONS:

OK
——————————

The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',
                     @filename1='D:\MSSQL10.MSSQLServer\MSSQL\Data\DBName.mdf',
                     @filename2='E:\MSSQL10.MSSQLServer\MSSQL\LOG\DBName_log.ldf'

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

References
[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.

Granting Execute Permissions

November 10, 2008 Leave a comment

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO 

Lost …

September 4, 2008 Leave a comment

Well it has been a while since I posted anything here, it seems that finding stuff to write about or stuff that I learn while playing with/studying SQL Server is too much to write about. But I will try to summarize it here… 1 day …

For now the biggest topic I been dealing with as of late, I think I laid it to rest but we’ll see. Is SQL Injection Attacks; and how is possible to do these injection attacks via web pages?

While dealing with this issue, it amazes me how many people didn’t understand what SQL Injection attack is; everyone I talked to the first statement they told me are the servers patched up. Did you get the latest security fix?

And no matter how I put it, I didn’t seem to get it through to people that was the case. So I been trying to write up SQL Injection history; I am sure someone else already has done this. But I am hoping this can give some people some insight maybe they didn’t have. Or more to educate people who believe SQL Injection is another “techie” IT guys use to avoid doing work.

Well I guess part of the problem in my case was it was done on Microsoft SQL Server; so naturally they all rather attack Microsoft weaknesses then go through their code. I heard this statement recently “Anyone can take care of SQL Server”; and I wonder if that is the case … maybe all my studying for SQL Server isn’t needed. And then again next issue that walks through the door I’ll be diving in my books again. So I think I’ll keep at it, and hope everyone around me changes their view point of SQL as environment I support changes and improves.

WIP: SQL Server Security Development, Testing, and Production

December 27, 2007 Leave a comment

Currently I have multiple SQL Server instances; with almost no standards to speak of. So trying to figure out what standards I want to define. And security is turning out of to be a big issue; I am pretty sure how I want to organize it. But fighting developers is not being easy.

For all servers, I want to rely on AD Security as much as possible, but there are expections where SQL Security is needed.

Layout I am thinking of:

Development

  • No user has SysAdmin rights on any server.
  • Developers have DBO on their respective database they are working on.
  • Trying to decide how to do this, give only selected few developers access to they database they are working on?
  • Give all developers access to the each development database?
  • No End Users have access to the development server or database.

Testing

  • No user has SysAdmin rights on any server.
  • Developers have same level of access as End User so they can help the end user troubleshoot issues.

Production

  • No user has SysAdmin rights on any server.
  • Developers have no access to production servers.
  • End user has required read/write rights on their databases.
Categories: Administration Tags:
%d bloggers like this: