How can I set read-only DB Connection Strings?

Question

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 read-write, and any attempt to change them (currently) crashes the SQL Server database.

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 read-only as it absolutely, positively can't change the DB data.

Bamby

The first answer you had was correct. – Wench

I think in this case the fact that you can't change the permissions of the credentials you are using actually IS your problem, since that would be the best solution... – Gasper

It may be my "problem", but there's absolutely nothing I can do about it, short of getting MS in to fix the SQL Server issue for free, out of office hours. It's one of those issues that might get resolved, but might not (it might involve a complete reload of the main IT systems, which is a huge job), so I need to work on the assumption that it won't. – Bamby

I can only mark one reply as the answer, can't I (I can only up-vote the other helpful ones)? – Bamby

Possible duplicate of set sql server connection to readonly? – Alda

Answer 1

What you have under your control is classes to acces code (L2S). I suggest to override in a partial class the SubmitChanges for your datacontext in order to do nothing ( or even throw an error!) (or implementing all extensibility methods InsertObject, UpdateObject or DeleteObject that belong to your datacontext)

Circumambient

Good idea, I'll put that in and test it - thx. Not as good as making the conn RO, but another layer to stop anything, at least. – Bamby

Answer 2

No, there is no way (that I know of). Unfortunately for you, the right way to do it would be to change the grants of the current user, or create a new user with only select privileges. I realize this is not the answer you are looking for but having a Sql Server that crashes when you try to change things in it seems to be a problem that is really worth looking into. Is it because you are using the "sa" account to connect? If so you should create another user and grant the appropriate permissions to the new user.

Vernalize

It's not an sa account, and it's an SEP (somebody else's problem) that I have no authority or jurisdiction over. I know they are trying to sort out the issue, but I think it's a fundamental problem with SQL server (2008?) so they may or may not be able to fix it - hence my wish to make the DB conn RO – Bamby

just checked and it's SQL Server 2005 – Bamby

Answer 3

It really depends on what database and DB provider you are using. Some allow readonly access on the connection string, some don't.

For example:

SQL Server 2005 CE, when using the .NET Compact Framework Data Provider for SQL Server Mobile, has a possible File Mode=Read Only; parameter. (see on connectionstrings.com).

SQL Server 2008, doesn't.

You can check more on connectionstrings.com.

Syphilis

Thx, that site is useful - it's full SQL server 2005 so I'm attaching to the DB rather than a file so that CE thing doesn't apply, unfortunately – Bamby

Answer 4

there's nothing you can do at the connection string level that prevents writes other than change the user - which you've already said you can't do.

In that case you simply have to do the utmost in your code to prevent any writes; i.e:

• any public layers should not expose update/delete/insert semantics or whatever.
• make any data layer classes sealed so that they cannot be overriden

However, there's still nothing stopping a programmer from coming along, ripping out your connection string, and sticking it inside their own Connection to perform writes.

You could, therefore, move the connection string somewhere else that only internal code knows how to access (it's still going to be text-file driven, though, don't use a code constant!); it's still not stopping anyone from using it - but it makes it a lot harder.

(added) I should explain why it does not protect it.

Leaving aside that the source of the connection string itself is likely to be accessible, even by protection with encryption libraries etc, there's nothing stopping me reflecting to your code and calling it, apart from trust levels. You might choose to go the whole obfuscation route to prevent me from deconstructing your code; but surely this level of paranoia is not required within your development house?

Ultimately, though, because it's 'SEP' (somebody else's problem) as you put it, and you have no control over that - if anybody asks you why, despite your best efforts, you can't guarantee that no writes will be performed, you can safely blame that 'somebody else'.

Polymath

Answer 5

Old question, but anyway, I suggest another way:

• Create a new DB in the same server/instance (better) or another one that has access to the main one (slower),
• Add the main DB as a Linked Server in your new DB, using your current credentials to access the original DB fromthe new one,
• Add views in the new DB, that access all the main DB tables that you may need,
• Add a trigger INSTEAD OF INSERT, UPDATE, DELETE on each and every view, that does exactly NOTHING,
• Change the connStr in your app to access the new DB instead of the original one.

Now, you app is fully R/O. There is no way any app acccessing the new DB would ever change data in the main DB.

NOTE:

• I'm not dealing here with side effects that may depend on your specific case, e.g. weather you use functions, SP, or other kinds of objects.
• If your new DB in in the same instance as the main one, the overhead is insignificant.
• As you will now connect to a different (yours) DB, you could also define your own credentials, e.g. a new user that has db_datareader and db_denydatawriter roles.

Wherefore