Path/File Access

May 10, 2011 at 4:42 PM

Does the solution support UNC and/or Network Share names?

Msg 50000, Level 16, State 1, Line 1System.Exception: SourceFileNameWithPath: "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName_Data.mdf" DestinationFileNameWithPath: "G:\MSSSQL\DATA\DBName_Data.mdf" ---> System.UnauthorizedAccessException: Access to the path is denied.   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)   at System.IO.File.Move(String sourceFileName, String destFileName)   at FileManipulationSQLCLRExternal.SharedObjects.MoveFile(String SourceFilePath, String SourceFileName, String DestinationFilePath, String DestinationFileName, Boolean OverwriteExistingFileFlag)   --- End of inner exception stack trace ---   at FileManipulationSQLCLRExternal.SharedObjects.MoveFile(String SourceFilePath, String SourceFileName, String DestinationFilePath, String DestinationFileName, Boolean OverwriteExistingFileFlag)   at FileManipulationSQLCLRExternal.StoredProcedures.MSPMoveFile(SqlString SourceFilePath, SqlString SourceFileName, SqlString DestinationFilePath, SqlString DestinationFileName, SqlByte OverwriteExistingFileFlag)

It does however work when I use just C:\Temp\Test.txt to G:\Temp\Test.txt

Coordinator
May 10, 2011 at 4:59 PM

In principle yes.  And when I last tested UNC yes it worked.  However, keep in mind that the SQLCLR is running in the security context OF SQL Server.  So if the login used for SQL server does not have access to a resource then the SQLCLR will not either.  This is particularly true of UNC paths.  And if the login is NOT a domain account you will likely have some issues reaching into other machines.

I have thought about adding some impersonation logic to allow for changing of the security context but have not had a chance.

Now, on the topic of network share names, I'm not entirely sure what you are refering to.  If it is mapped drives then the answer is, MAYBE.  Mapped drives may or may not be available to a SQL server process and therefore are not a good idea to depend on.  If you were refering to something else please let me know.

May 11, 2011 at 9:06 AM

Network Share Names - yes, I was referring to mapped drives.

Have you tried using your solution to move MDF files from one location to another:

USE master

go

sp_detach_db 'DBNameGoesHere' 

GO

[dbo].[MSPMoveFile] 
--(with parameters)

WAITFOR DELAY '00:00:05'
go 
--(to allow for the move to complete)

USE master  go

sp_attach_db 'DBNameGoesHere', 'G:\MSSQL\Data\DBNameGoesHere.mdf',    'G:\MSSQL\Data\DBNameGoesHere_1.ldf' 
go   

USE [DBNameGoesHere]

go

sp_helpfile 

go

I can get it to work but have to use advanced permissions to "Replace all existing inheritable permissions on all descendants..."

Then when I try again for another database, I have to repeat, UAC is turned off (Windows Server 2008 Standard (x64), SQL Server 2008)

 

Coordinator
May 11, 2011 at 2:21 PM

I haven't tried copying db files.  So as I read that, you are copying the files to the same directory or a different one?  And then try to re-attach them to the same instance or a different one?  When you replace the permissions you are doing it on the files?

One of the things about a file move is that the permissions and attributes move with the file in NTFS, this is often noticed when moving an uncompressed file into a compressed directory, it isn't compressed by that move where if you copied it it would be. 

I am looking at this now.  However, my initial thought is that the problem is tied to the security in place on the file system.  The move uses the .net built-in utility to do that move, but it occurs in the security context of the SQL server.

Does your SQL server login as a built-in account, a local account, or a domain account?

May 11, 2011 at 4:20 PM
Edited May 11, 2011 at 4:26 PM
novaconceptsltd wrote:

I haven't tried copying db files.  So as I read that, you are copying the files to the same directory or a different one?  And then try to re-attach them to the same instance or a different one?  When you replace the permissions you are doing it on the files?

One of the things about a file move is that the permissions and attributes move with the file in NTFS, this is often noticed when moving an uncompressed file into a compressed directory, it isn't compressed by that move where if you copied it it would be. 

I am looking at this now.  However, my initial thought is that the problem is tied to the security in place on the file system.  The move uses the .net built-in utility to do that move, but it occurs in the security context of the SQL server.

Does your SQL server login as a built-in account, a local account, or a domain account?

Q1 - Different file location

SourceFileNameWithPath:

"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName_Data.mdf"

DestinationFileNameWithPath:

"G:\MSSSQL\DATA\DBName_Data.mdf

Q2 - Re-attach the same DB with the new file location

Q3 - Parent Folder in my example this would be C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

I then have to right click the folder ->Properties -> Security -> Advanced -> Edit -> Check the tick box "Replace all existing inheritable permissions on all descendants..."

It then works

Q4 - Domain Account of which I am sysadmin.

Coordinator
May 11, 2011 at 6:08 PM

Ok.

As for Q4, your SQL server >>>SERVICE<<<logs in as YOU, just making sure.

You know I had a wierd case a while back that was unrelated to using this tool but was related to attaching DBs.  I had a trusted login that had sysadmin rights but I could just not get SQL to attach the db, I logged in as SA and it worked..  Probably not your problem but I figured I'd throw it out there.

As for Q3, you are replacing permissions on the Source location?  Is that a typo?

After you move the files what does the security look like for them?  Is it the same as when they were in the source location?  I'd be curious if they changed and how they differ from the parent.  Who is the owner before and after?

Where I'm having trouble determining the problem is that if you can mount the db after you change permissions to force the children to match the parent at the destination it works.  What this tells me is that SQL has permissions at both sides. 

Is any of that clear?

May 12, 2011 at 9:18 AM
Edited May 12, 2011 at 9:41 AM

Q3 - No typo, the source was the issue.

After the move, the file/folder security is identical (after applying the "fix" mentioned previously.)

 

"You know I had a wierd case a while back that was unrelated to using this tool but was related to attaching DBs.  I had a trusted login that had sysadmin rights but I could just not get SQL to attach the db, I logged in as SA and it worked..  
Probably not your problem but I figured I'd throw it out there."

Low and behold, after logging in as SA and tried the whole process - it worked!  Thanks for throwing it out there!

 

 Whole process being 

 Detach
 Copy/Move/Delete
Attach
sp_helpfile (to verify move)

Thanks again for your solution and support!

 

Edit:

***   If the database name starts with a number, the process fails - including using SA   ***