none
SqlFileStream - OpenSqlFileStream: Access is denied in C# RRS feed

  • Question

  • Hi Guys,

     

    I'm really struggling with this one....  I'm seeing this Pop Message while debbuging. 

     

     

    Source Code as shown below.  It starts when the it hits the line "objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);" as shown below with an arrow.

     

      private void FileStreamInsert(string nric)
            {

                DataTable pfdrec1 = dbaccess.DataSet.Tables["pfdrec"];
                this.dbaccess.ReadSQL("MaxID", "Select ISNULL(MAX(SystemNumber),0) + 1 AS sysno FROM DOCFILES");
                DataRow MaxID = dbaccess.DataSet.Tables["MaxID"].Rows[0];
                int count = Convert.ToInt32(MaxID["sysno"]);
                SqlConnection objSqlCon = null;
                SqlCommand objSqlCmd = null;
                SqlDataReader rdr = null;
                SqlFileStream objSqlFileStream = null;
                SqlTransaction objSqlTran = null;

                foreach (DataRow dr1 in pfdrec1.Rows)
                {

                    if (dr1.RowState != DataRowState.Deleted)
                    {
                        count = count + 1;


                        byte[] buffer = new byte[(int)File.OpenRead(dr1["templocation"].ToString()).Length];
                        if (File.OpenRead(dr1["templocation"].ToString()).Length >= 0)
                        {
                            try
                            {
                                objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
                                objSqlCon.Open();
                                objSqlTran = objSqlCon.BeginTransaction();

                                objSqlCmd = new SqlCommand("FileAdd", objSqlCon, objSqlTran);
                                objSqlCmd.CommandType = CommandType.StoredProcedure;

                                SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);
                                objSqlParam1.Value = count;

                                SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar, 4);
                                objSqlParam2.Value = ".zip";

                                SqlParameter objSqlParam3 = new SqlParameter("@nric", SqlDbType.NVarChar, 25);
                                objSqlParam3.Value = nric;

                                SqlParameter objSqlParam4 = new SqlParameter("@trackingno", SqlDbType.NVarChar, 50);
                                objSqlParam4.Value = dr1["trackingno"].ToString();

                                SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
                                objSqlParamOutput.Direction = ParameterDirection.Output;

                                objSqlCmd.Parameters.Add(objSqlParam1);
                                objSqlCmd.Parameters.Add(objSqlParam2);
                                objSqlCmd.Parameters.Add(objSqlParam3);
                                objSqlCmd.Parameters.Add(objSqlParam4);
                                objSqlCmd.Parameters.Add(objSqlParamOutput);

                                objSqlCmd.ExecuteNonQuery();

                                string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();

                                objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
                                byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();


                                objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write); <--- Error from here



                                objSqlFileStream.Write(buffer, 0, buffer.Length);
                                objSqlFileStream.Close();

                                objSqlTran.Commit();

                               
                                objSqlTran.Dispose();
                                objSqlCmd.Dispose();
                                objSqlFileStream.Dispose();

               
                            }
                            catch (SqlException ex)
                            {

                            }
                            finally
                            {
                                objSqlCon.Close();
                            }
                        }
                    }
                }

            }

     

    Thank you soo much.

    Sunday, September 4, 2011 9:49 PM

Answers

  • Nevermind I found the answer already.  The old Connection string was:

     

    connectionString="Server=localhost\sql2008;Initial Catalog=PG;Persist Security Info=True;User ID=sa;Password=sa123"

     

    And the working one is:

     

    connectionString="Server=LOCALHOST\SQL2008;Database=PG;User ID=sa;Password=sa123;Trusted_Connection=False;Integrated Security = true;"

    Monday, September 5, 2011 2:16 AM

All replies

  • Nevermind I found the answer already.  The old Connection string was:

     

    connectionString="Server=localhost\sql2008;Initial Catalog=PG;Persist Security Info=True;User ID=sa;Password=sa123"

     

    And the working one is:

     

    connectionString="Server=LOCALHOST\SQL2008;Database=PG;User ID=sa;Password=sa123;Trusted_Connection=False;Integrated Security = true;"

    Monday, September 5, 2011 2:16 AM
  • That is correct, FILESTREAM requires integrated security. SQL Server authentication is not supported.
    Tuesday, October 4, 2011 3:57 AM