Level 75
Feb 23, 2023
5
Level 6
Can't Query Database With MS SQL Read Only Account?
Hello! I am developing an internal app where the plan was to have 2 MS SQL 2016 database connections. One is our main "app" database and the other database is a LOB application from another vendor we don't want to mess with.
The main app connection works great without any issues.
The LOB database is mission critical and we can't risk a 3rd party app messing with the data. So any access to it from a 3rd party should be read only.
So I created a read only user on that database (public server role with the db_datareader role on the database itself) and added it to the Laravel database config and tried to run a basic select query against that DB. Unfortunately I got a login error: SQLSTATE[28000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'laravel'. (Connection: foundation_sqlsrv, SQL: select * from jobs)
I verified I can run this query with this same account against the same database using SQL Management studio. But in laravel it fails.
Both that app database that works and this LOB database that gets the login failed are on the same MSSQL host (different MS SQL server instances).
Does laravel not allow read only SQL accounts? Or am I doing something wrong?
Edit:
Just to further isolate this as a laravel issue I connected directly to SQL using PDO in my web.php router using the same ENV variables I used in config/database.php and it worked fine:
Route::get('/test', function () {
$hostname = env('DB_HOST_FOUNDATION');
$username = env('DB_USERNAME');
$password = env('DB_PASSWORD');
$dbName = env('DB_DATABASE_FOUNDATION');
$odbc="sqlsrv:Server=$hostname;Database=$dbName;";
$conn = new PDO($odbc, $username, $password);
$query = 'select * from jobs';
$stmt = $conn->query( $query );
//$jobs = DB::connection('foundation_sqlsrv')->select('select * from dbo.jobs');
return inertia('test', $stmt->fetch( PDO::FETCH_ASSOC ));
});
Level 6
@jlrdw Thanks! But unfortunately that doesn't work, Still get same error. Here is my database.php after adding the ApplicationIntent.
Also this talks about replication groups and that's not really what I'm doing so not sure this is the same issue that I'm having.
Is there some documentation on this and what permissions laravel actually needs?
'foundation_sqlsrv' => [
'driver' => 'sqlsrv',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST_FOUNDATION', 'localhost'),
'port' => env('DB_PORT', '1433'),
'database' => env('DB_DATABASE_FOUNDATION', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'ApplicationIntent' => 'ReadOnly',
Level 75
@mr_reboot have you thought of implementating authorization so only the user /s allowed can interact?
But can you set the database readonly in SSMS? I also suggest a search of some Microsoft articles.
Level 6
@jlrdw unfortunately authorization isn't a solution here. We simply can't have a 3rd party app having write permissions on this DB.
I can't set the DB to read only as this is a live database being used by another application.
Does Laravel really not allow read only accounts for SQL? Seems very odd but I don't know what it's doing behind the scenes (one of the reasons we don't want it having write access to the LOB database we don't control, can't risk me making a mistake screwing up our entire financials platform).
Level 6
Just to further isolate this as a laravel issue I connected directly to SQL using PDO in my web.php router using the same ENV variables I used in config/database.php and it worked fine. I might just need to revert to this if there isn't a better solution.
For anyone looking to do this in the future:
Route::get('/test', function () {
$hostname = env('DB_HOST_FOUNDATION');
$username = env('DB_USERNAME');
$password = env('DB_PASSWORD');
$dbName = env('DB_DATABASE_FOUNDATION');
$odbc="sqlsrv:Server=$hostname;Database=$dbName;";
$conn = new PDO($odbc, $username, $password);
$query = 'select * from jobs';
$stmt = $conn->query( $query );
//$jobs = DB::connection('foundation_sqlsrv')->select('select * from dbo.jobs');
return inertia('test', $stmt->fetch( PDO::FETCH_ASSOC ));
});
Don't know if this is the solution and will await more feedback. Thanks all!
Please or to participate in this conversation.