How can I set read-only DB Connection Strings?

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 vario开发者_开发百科us 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.

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.

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)

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.

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'.