OpenFileStream returns access denied

Question

Hello All,

it's me again with a newbe problem.

I'm trying to access a FILESTREAM on a remote MSSQL2008 server. I have the transaction token the filename on the server got by the query with the PathName() function on the data field.

Unfortunately, when I try to retrieve the handle to the file on the server for reading it returns an invalid handle and the GetLastError functions returns "Access denied (5)".Is there something on the server I missed to set up? Guest account is enabled. Filesharing is set to "simple file sharing".Is there something I missed to set up?

Thanks a lot and have a nice day!

Best Regards.

Marc

Answer 1

I found that a possible solution could be to add "Integrated Security=true;" t the connection string could help, but it was not so. It still returns the access denied error. :-(((

Thanks.

Marc

Answer 2

Hello,

Please, could you post your connection string and the code related to the OpenFileStream ?

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx

http://blogs.msdn.com/b/blogdoezequiel/archive/2011/02/15/loose-notes-on-filestream.aspx

Are you sure that you are using OpenFileStream ( i would think OpenSqlFileStream ) ?

Are you sure that the user you are using to connect has the rcorrect access permissions to the files and the folders where the Filestream data are stored ?

Please, could you tell us what is the version ( year + last installed service pack ) and the edition of your SQL Server ?Which is the operating system on which the SQL Server is installed ? Is your own application connect to a remote or local SQL Server ?

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

PS : i suppose that you know this link :

http://msdn.microsoft.com/en-us/library/bb933972(v=sql.105).aspx ( i gave the SQL Server 2008 R2 as you have not given any information about your SQL Server instance )

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hello,

thanks you very much for your reply.

The user to connect to the db is a server level user, it is not a windows account. I'm using that because windows authentication doesn't work. I succeeded only by using the server accounts to login on the server. That means, remote user authentication doesn't work, it works only localy!!! What can I do for that?

The connection string is: "DRIVER={SQL Server};SERVER=%s,%d;DATABASE=%s;UID=%s;PWD=%s;Integrated Security=true;"

You're right, the function is: OpenSqlFilestream

The server is an Microsoft SQL Server Express Edition on a WinXP 32 bit. Version: 10.0.2531.0

The application connects remotely to the server. Remote host is a Win7 OS.

Thanks you very much and have a nice day!

Marc

Answer 4

He he,

here is the code snippet I'm using:

SQLRETURN r;
    SQLCHAR transactionToken[1024];
    SQLTCHAR srcFilePath[1024];
    SQLINTEGER cbTransactionToken, cbsrcFilePath;

    _TCHAR* sql = new _TCHAR[1024];

    _stprintf(sql, _T("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() as T, DATA.PathName() AS PathName FROM setup_data WHERE ID = %d;"), file_id);

    SQLHANDLE st = NULL;
    
    SQLSetConnectAttr(p_connection, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);

    SQLAllocHandle(SQL_HANDLE_STMT, p_connection, &st);
    
    SQLExecDirect(st, sql, SQL_NTS);

    r = SQLFetch(st);
    if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO)
    {
        close();
        return CODBC_ERR_SELECT_FILESTREAM;
    }

    r = SQLGetData(st, 1, SQL_C_BINARY, transactionToken, sizeof(transactionToken), (SQLLEN*)&cbTransactionToken);
    if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO)
    {
        commitTransaction();
        close();
        return CODBC_ERR_SELECT_FILESTREAM;
    }

    _TCHAR path_on_server[1024];
    SQLLEN len = 0;
    r = SQLGetData(st, 2, SQL_C_TCHAR, path_on_server, sizeof(path_on_server), &len);

    
    HANDLE srcHandle =  OpenSqlFilestream(
                            path_on_server,
                            SQL_FILESTREAM_READ,
                            0,
                            transactionToken,
                            cbTransactionToken,
                            0);

    
    if (srcHandle == INVALID_HANDLE_VALUE)
    {
        DWORD err = GetLastError();
        return -1;
    }

The value of err at the end is always "Access Denied". On the other side the log entry on the server is "The current user cannot use this FILESTREAM TARNSACTIO context. To optain a valid FILESTREAMtransaction context, use GET_FILESTREAM_TRANSACTION_CONTEXT."

Hmmm I thought this is exactly what I did, but it seems to to be so......

I don't know what I can do.....

Answer 5

Hello,

I have reread your both last posts.

I have been surprised by the version of SQL Server 2008  Version: 10.0.2531.0 it is far from my own 2008 SP3 ( 10.0.5000 ). I am searching whether the SP3 could solve your problem.

I suppose that your code has been written in VC++ , but which version ?

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.