[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

System.Exceptin: System.NotSupprotedException: The given path's format is not supported

Posted on 2007-08-02
7
Medium Priority
?
774 Views
Last Modified: 2013-11-07
Products: VS2005, SQLServer2005, WinServer2003-1="server," WinServer2003-2="client"
Goal: Store documents(.doc, .xls, .mp3 etc.) as VARBINARY(MAX) records in SQL Server db.
WinApp: Collect Path\File.ext and miscellaneous fields, pass to Sproc via ADO.Net (Params.Add, ... ExecNonQuery)
SQLServer: Consume .dll to yield methods as stored procedures. The .dlls use stream reader and writer to read/write Path.File. It is the stream reader that fires System.Exception (described in title and below)
Development Environment: Win2003. Everything works as expected when "client" and SQLServer are  on the same box.
Generic Problem (at deployment): SQL Server on box2 doesn't access files on box1-client.
Clue 1: SQL Server (box1) cannot connect to .dll on client development box2. Solution 1: copy .dll from dev. box2 and past into shared folder on Server box2.
Clue 2: "System.Exception: System.Data.SqlClient.SqlException ... from 'clrSpStoreDoc' (my clr sproc) System.Exception: System.NotSupprotedException: The given path's format is not supported..."
The paths it complains about: "C:\directory\file.ext", "\\MachineName\C:\directory\file.ext", "38.22.36.18\C:\directory\file.ext."  
Specific Question: How do I get the two boxes to play together?
Hypothesis 1: Ill-formatted file locater string. Hypothesis 2: Win Security issue---need to elevate permission on server so it has permission on client (??nonsense??).
Worst scenario: WinApp copies file from client to shared directory on server, then launches sproc which reads file from it's local directory and writes to db.

This description deliberately high level. Need a direction to drill into. Will answer questions and explore suggestions in good-faith dialog.
0
Comment
Question by:brumagemMX
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 19623028
\\MachineName\C:\directory\file.ext is not valid UNC path.
It has to be \\MachineName\ShareName\directory\file.ext.
You can sometimes use Sharename "C$" for root of C drive. But it is only for Administrators and can be disasbled.
0
 

Author Comment

by:brumagemMX
ID: 19625115
\\MachineName\ShareName\file.ext  = \\Adiemus\Jeff\Test.txt
Share Name = Jeff
   Permissions: Admin=FullControl; Everyone=FullControl
   Security: Admin, CreatorOwner, Everyone, System, Users (Adiemus\Users) = FullControl

Note: Server is Spanish Language Edition, software developed in/on English edition box

>Debug.print ex.tostring
"System.Data.SqlClient.SqlException: Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'clrSpCargarDoc':
System.Exception: System.UnauthorizedAccessException: Access to the path '\\Adiemus\Jeff\Test.txt' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
   at System.IO.FileInfo.OpenRead()
   at DocumentoDbIO.ECOSUR.Documento.StoredProcedures.clrCargarDoc(String tipoDeVinculacion, Int32 IDDocumentoVinculacion, Int32 IDTipoDocumento, String archivoPath, String titulo, Int32 anoPublicaction, String autores)...
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19636647
Now it looks like pure Permissions problem.
What is your SQL Service account (the one SQL is running under)? Your DLL will run in same context and it will have samer permissions. If it is SYSTEM (worst of all possibilities) it has no network access.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:brumagemMX
ID: 19641563
I hope you had a plesant and/or productive weekend!

The answer to your question, I think, is LocalSystem since SERVICES reports that as the logon for MSSQL. Perhaps I didn-t understand the question.

You may find (more) relevant information here: 08.03.2007 at 01:45PM CDT, ID: 22740833
"Title: WHY? FileStream can't open file on second box but command prompt can copy that file
where tedbilly lead me to impersonation...

Here is the current error after implementing impersonation. Code snippet follows error.
>Debug.print ex.tostring
"System.Data.SqlClient.SqlException: Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'clrSpCargarDoc':
System.Exception: System.Exception: FileStreamError in clrCargarDoc. CallerNAME=SINBAD\Administrator. Path=\\Adiemus\Jeff\Test.txt. ERROR::System.UnauthorizedAccessException: Access to the path '\\Adiemus\Jeff\Test.txt' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
   at System.IO.FileInfo.OpenRead()
   at DocumentoDbIO.ECOSUR.Documento.StoredProcedures.clrCargarDoc(String tipoDeVinculacion, Int32 IDDocumentoVinculacion, Int32 IDTipoDocumento, String archivoPath, String titulo, Int32 anoPublicaction, String autores)
   at DocumentoDbIO.ECOSUR.Documento.StoredProcedures.clrCargarDoc(String tipoDeVinculacion, Int32 IDDocumentoVinculacion, Int32 IDTipoDocumento, String archivoPath, String titulo, Int32 anoPublicaction, String autores)

Code snippet...
    Public Shared Sub clrCargarDoc(ByVal tipoDeVinculacion As String, ByVal IDDocumentoVinculacion As Int32, _
                                   ByVal IDTipoDocumento As Int32, ByVal archivoPath As String, _
                                   ByVal titulo As String, ByVal anoPublicaction As Int32, ByVal autores As String)
      Try
        Dim doc As Byte() = Nothing
        Dim impersonationContext As WindowsImpersonationContext = Nothing
        Dim fileName As String = ""
        Dim callerName As String = ""

        Try
          Dim callerIdentity As WindowsIdentity = SqlContext.WindowsIdentity
          If (Not (callerIdentity Is Nothing)) Then
            callerName = callerIdentity.Name.ToString
            impersonationContext = callerIdentity.Impersonate

            Dim archivoInfo As FileInfo = New FileInfo(archivoPath)
            fileName = archivoInfo.Name
            Dim fileStream As FileStream = archivoInfo.OpenRead()
            ReDim doc(Convert.ToInt32(archivoInfo.Length) - 1)
            fileStream.Read(doc, 0, Convert.ToInt32(archivoInfo.Length))
            fileStream.Close()
            impersonationContext.Undo()
          End If
        Catch ex As Exception
          Dim s As String = impersonationContext.ToString
          impersonationContext.Undo()
          Throw New Exception("FileStreamError in clrCargarDoc. CallerNAME=" + callerName + ". Path=" + archivoPath + ". ERROR::" + ex.ToString)
        End Try

Thank you for your suggestions!
0
 
LVL 12

Expert Comment

by:patrikt
ID: 19643714
I think that impersonation will not help there. LocalSystem (BUILTIN\SYSTEM) account has denied all access outside its own box. It is denied by system core not by permissions and it cann't be enabled by permissions.

If you can try to change SQL service account. Best is to create new user and asign permissions on local box, then use it as service account for SQL service.
It is just a test, I'm not sure that it will be solution. Consider impact of changing this if it is not testing only server.
0
 

Author Comment

by:brumagemMX
ID: 19647541
Thank you, patrikt.
Sinbad is a mature production server "and we aint gonna experiment..."
We discussed a file shared to Guest but that puts a hole in our open environment.
It appears that the solution is a web app rather than a win app (I can't believe I'm writing this!).
This has been a good lesson in security for me. I'm now accepting this solution.
Thanks.
0
 
LVL 12

Accepted Solution

by:
patrikt earned 2000 total points
ID: 19652570
OK, good luck in future developement :)

On next production server you will be near at setup time remember to change service account. The LocalSystem, as other builtIn accounts, is bad option and trap for future tasks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question