Read Only DB Connection String

.NET
1

This seems like a really silly question, but I’ve had a search around and I can’t find anything about this.

I’ve got a DB connection string that I’m creating in my web.config:-

    <connectionStrings>
    <add name="DBConn" connectionString="Data Source=<db svr>;Initial Catalog=<dbname>;Integrated Security=True" providerName="System.Data.SqlClient />
    </connectionStrings>

or

   Data Source=<db svr>;Database=<db name>;User ID=<uname>;Password=<pword>;

but I need this connection to be read only. I’ve defined all my linq objects with only gets on their properties, and none of my (MVC) repository classes have .SubmitChanges() methods in them so I’m 99% sure the system can’t update this DB, but I would also like to set my DB connection to be RO if at all possible. I realise that ideally this should be done at the SQL server end and the user should be made RO, but that (for various reasons, out of my control) can’t be done, so I wanted to lock down my connection as the app mustn’t write to the DB.

Is there a “readonly” parameter I can apply to the connection string so that it would throw an error or discard the data if any updates were attempted?

Just to reiterate (the 1st answer I had, when asking this, on another forum was “change your DB credentials”) I cannot, in any way, change the DB access credentials, these are RW and any attempt to change them (currently) crashes the SQL server DB. This is not my problem, and I can’t look at resolving that issue, so that’s why I want to look at making the DB connection RO as it absolutely, positively has to kill every… errr, I mean absolutely, positively can’t change the DB data.

Cheers

MH

2

From what (or who) are you trying to prevent this, users or other developers? If the former, don’t provide them any opportunities to do so. If the latter, good luck.

Cheers,
D.

3

Developers - doing accidental/bug-induced updates. No-one would override any DB connection string settings and no-one should try to update the DB intentionally, but the best laid plans, as they say.

4

This isn’t something one handles in the connection string per-se, but rather in one of two ways:

a) Go old school DBA style and don’t grant the DB user in question anything save SELECT rights on the database schema in question.
b) The generated linq classes have some events that fire before update. Make said event handler throw a NotImplementedException, preventing any calls to SubmitChanges() from actually firing.

Either one will drive the poor fool who has to maintain this years later batty.

PS: Just read to the bottom of the initial post. If that is the case, then you probably are writing somewhere as removing write capabilities makes the program crash. So this probably isn’t going to work. It might help to trust your developers. They are on your side after all.

5

No, changing the privs makes the whole database crash (good old MicroSoft), not my app. It’s not a matter of trust, the DB absolutely cannot be written to by my app, under any circumstances, it’s mission critical and any mistake is bad news - hence I wanted to lock down the possibility of this happening as tight as possible in as many places as I can because it can’t be done in the right place - the DB privs.

6

Why can’t a new SQL user be created with just RO rights? Then connect as that user instead of using integrated security. This shouldn’t break anything in terms of exisiting functionality.

7

Can’t - did you not see the last paragraph of my original post

Just to reiterate (the 1st answer I had, when asking this, on another forum was “change your DB credentials”) I cannot, in any way, change the DB access credentials, these are RW and any attempt to change them (currently) crashes the SQL server DB

There is an issue with MS SQL Server so that new-user=no-more-working-DB. This may, or may not be resolvable, and either way, is out of my hands. Hence me looking to the DB connection, as the next link in the chain of data-access.

8

Having added hundreds of users to MSSQL servers of various sorts, I really don’t understand how adding a user makes the database server crash unless there is something horribly wrong with the database server and you are living on borrowed time anyhow.

Perhaps you could define exactly what happens when you “crash the whole sql server DB.” Fixing that problem will likely be easier than faking a read only database user in the application layer.

9

I agree with you totally, but unfortunately the server is nothing to do with me, and I have no access to it, beyond looking at the table structures and data so I know nothing more about the problem than I have posted. The DBA is looking into the problem, but having seen strange issues like this before it may not be solved in the near future - if so then that is a situation I will just have to live with. My problem is to make sure the app doesn’t (and preferably can’t) update the data - hence my question and responses.