Error: Access is denied. (os error 5)

[jmacak-at-dl]

I can't figure out why I keep getting this error when trying to run odbc2parquet. In my experience, this error is usually a result of folder permissions, but that doesn't seem to be the case (or I'm giving permission to the wrong user).

Here's the command I'm running and its output:

PS C:\Users\Administrator> odbc2parquet -v query --connection-string $connStr $outpath $query
2023-04-12T16:31:56-04:00 - WARN - State: 01000, Native error: 5701, Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'db_name'.
2023-04-12T16:31:56-04:00 - WARN - State: 01000, Native error: 5703, Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english.
2023-04-12T16:31:56-04:00 - INFO - Database Managment System Name: Microsoft SQL Server
2023-04-12T16:31:56-04:00 - INFO - Memory usage per row is 269192 bytes. This excludes memory directly allocated by the ODBC driver.
2023-04-12T16:31:56-04:00 - INFO - Batch size set to 7977 rows.
Error: Access is denied. (os error 5)

• Running PowerShell as an administrator does not resolve the issue.
• I have verified the connection string works by running the same query via New-Object System.Data.Odbc.OdbcConnection, and received results.
• Likewise, the outpath and query are both functional.
• When I run odbc2parquet -vv query, the results seem to indicate that odbc2parquet is able to read the schema of the table I'm pointing to in the query.
• I mentioned previously that I've run into this error with other scripts, and resolving the issue involves giving permissions to NT Service\MSSQLSERVER to your database files. But they're not resolving the issue in this case:

PS C:\Users\Administrator> Get-Acl $logFile | format-list


Path   : Microsoft.PowerShell.Core\FileSystem::D:\LOG\db_name_log.ldf
Owner  : NT SERVICE\MSSQLSERVER
Group  : NT SERVICE\MSSQLSERVER
Access : NT SERVICE\MSSQLSERVER Allow  FullControl
         NT AUTHORITY\SYSTEM Allow  FullControl
         BUILTIN\Administrators Allow  FullControl
         BUILTIN\Users Allow  FullControl
Audit  :
Sddl   : O:S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003G:S-1-5-80-3880718306-3832830129-1677859214-2
         598158968-1052248003D:AI(A;ID;FA;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)(A;ID;FA;;;
         SY)(A;ID;FA;;;BA)(A;ID;FA;;;BU)



PS C:\Users\Administrator> Get-Acl $dbFile | format-list


Path   : Microsoft.PowerShell.Core\FileSystem::D:\DATA\db_name.mdf
Owner  : NT SERVICE\MSSQLSERVER
Group  : NT SERVICE\MSSQLSERVER
Access : NT SERVICE\MSSQLSERVER Allow  FullControl
         NT AUTHORITY\SYSTEM Allow  FullControl
         BUILTIN\Administrators Allow  FullControl
         BUILTIN\Users Allow  FullControl
Audit  :
Sddl   : O:S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003G:S-1-5-80-3880718306-3832830129-1677859214-2
         598158968-1052248003D:AI(A;ID;FA;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)(A;ID;FA;;;
         SY)(A;ID;FA;;;BA)(A;ID;FA;;;BU)

Have I done something wrong in the installation? I don't have Rust installed. Is there an account other than NT SERVICE\MSSQLSERVER that I should be giving permissions to the folder?

[pacman82]

Hello @jmacak-at-dl ,

let's see.

odbc2parquet -v query --connection-string $connStr $outpath $query

Command seems fine. 'course I can't tell what the environment variable are doing.

• Running PowerShell as an administrator does not resolve the issue.
• I have verified the connection string works by running the same query via New-Object System.Data.Odbc.OdbcConnection, and received * results.
• Likewise, the outpath and query are both functional.
• When I run odbc2parquet -vv query, the results seem to indicate that odbc2parquet is able to read the schema of the table I'm pointing to in the query.

Thanks for taking the time to debug the issue beforehand and sharing the steps with me.

I mentioned previously that I've run into this error with other scripts, and resolving the issue involves giving permissions to NT Service\MSSQLSERVER to your database files. But they're not resolving the issue in this case:

Interessting, I would know little about all that, though.

Have I done something wrong in the installation? I don't have Rust installed.

No. Copying the executable is enough. Rust is a compiled, not interpreted language.

Is there an account other than NT SERVICE\MSSQLSERVER that I should be giving permissions to the folder?

I cannot answer that. What I can say though, is that the most likely cause of error is that odbc2parquet is not able to write the file there you want that. For this it uses the permissions of the user which starts odbc2parquet. Ideas for debuging:

• Could you create any file with the user in this directory?
• Can odbc2parquet write its output into another directory?

Best, Markus

[jmacak-at-dl]

Thank you so much for taking the time to respond!

I am able to create a file in the outPath directory as the same user. Also, changing the outPath to a different location (I tried simply the Documents folder) resulted in the same error.

However, literally as I was trying to collect information for this comment, I got the script into a working form, and I think I've been able to isolate the issue.

Here's the full script:

$driver = 'ODBC Driver 17 for SQL Server'
$user = 'user'
$password = 'pass'
$server = 'server'
$db = 'database'
$table = 'table'

$query = "SELECT * FROM $table"
$outpath = "E:/DataPull/ParquetFiles/$db/$table"

$connStr = "Driver={$driver};Server=$server;UID=$user;PWD=$password;Database=$db"
Write-Host "Connection String: $connStr"
Write-Host "Query: $query"
Write-Host "Outpath: $outpath"

.\odbc2parquet.exe -v query --connection-string $connStr $outPath $query

The above script runs as expected. However, this edit results in the "Access is denied" error:

$outpath = "E:/DataPull/ParquetFiles/$db/$table/"

I was expecting the script to generate a folder of part.#.parquet files, not a single parquet file, and I think that's what was causing the error. Thank you for your help!

[pacman82]

odbc2parquet 0.15.4 is just released. It features more context in the error message in case file creation fails. My hope is that similar issues are debugged faster in the future.

Cheers, Markus

[jmacak-at-dl]

Thank you for the update!

[pacman82]

Happy that the issue is resolved for you. I will think about my error handling in these kinds of situation, in order to give a better message in the future.