C# – Write file on client and read same file from inside the database

We had a job that an enterprise software vendor was running that failed on permissions denied. They asked us to open the file share to anyone without a password. Our awesome SQL Server DBA pushed back, thankfully. We came up with what is below to troubleshoot.

This allowed us to reproduce the condition, the root cause of which was traced back to a subdomain issue. Quick POC’s like this enable you to troubleshoot much more quickly, even with monolithic enterprise apps. This is only true if you can mimic the “big picture” behavior of the app. In this case, it was read a file, write the file, and then insert it in the database from the same location you wrote the file on the client.

using System.IO;
using System;
using System.Data.SqlClient;

//C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe 

class WriteTextFile {
  static void Main() {
    string[] lines = { "One", "Two", "Three" };
    System.IO.File.WriteAllLines(@"\\cmhfiler2\amspromo\sdh_poc.txt", lines);
    SqlConnection conn = new SqlConnection("Data Source=dbserver;Initial Catalog=dbname;Integrated Security=True;");
    conn.Open();
    SqlCommand command = new SqlCommand("select * from openrowset(bulk '\\\\cmhfiler2\\amspromo\\sdh_poc.txt', single_clob)", conn);
    SqlDataReader rdr = command.ExecuteReader();  
    while (rdr.Read())  {              
      Console.WriteLine("Line is " + rdr[0]);
    }  
  }
}