Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mr Fett
Starting Member
28 Posts |
 Posted - 2008-07-31 : 14:52:48
|
| Hi all,I've been tearing my hair out on this one for some time now - apologies if there is a simple fix, I haven't been able to find one.My situation is this: I have a number of websites that use SQL Server, in the past I've simply used the SERVER_IUSR account to give READ/WRITE access to the ASP scripts on it (I properly sanitize all user input to avoid SQL Injection and XSS).With the huge increase in attacks on the server however, I want to give the _IUSR account NO WRITE access (just read) and where I need to update or insert data, I wanted to use SQL Authentication for the particular connection (granting the SQL user read/write access).Unfortunately I can't get this to work at all - SQL Server seems to just be disregarding my connection string:Set readWriteConn = Server.CreateObject("ADODB.Connection")readWriteConn.Mode = 1readWriteConn.ConnectionString = "DSN=myDatabase; User Id=fettSQLUser; Password=thePassword; Trusted_Connection=False;"readWriteConn.CursorLocation = 3readWriteConn.OpenThe script runs fine extracting all the read only data but when I attempt to update a page counter using the connection string above I get the following error:Saying "UPDATE permission denied on object ......... owner 'dbo'."I'm baffled - I've ensured that SQL Server is running in mixed mode authentication, I've tried removing IUSR completely from the database and just connecting using my new readWrite SQL Server authentication string but no luck - it just wont do it unless I also give IUSR read/write permissions.It's as thought the SQL Authentication is sat on top of the IUSR authentication (or should I say that the request goes through both accounts).Any ideas anyone?Thanks!Bob |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Mr Fett
Starting Member
28 Posts |
Posted - 2008-07-31 : 15:40:30
|
quote:
Originally posted by jsmith8858
You are using a DSN. Either change the definition in your DSN, or don't use a DSN and specify all of the connection properties (provider, server, database, etc) in the connection string.see:http://www.connectionstrings.com/?carrier=sqlserverif necessary.- Jeffhttp://weblogs.sqlteam.com/JeffS
Jeff you are fast becoming my savior - don't worry, I'll try not to make a habit of it ;-)I'll give this a go and report back for others - I must admit, I didn't realise there was a connection between the IIS/IUSR security and the connection method!Thanks! |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-07-31 : 16:03:20
|
| There is no connection between the IIS/IUSR account, and the connection method. The problem stems from the Trusted Connection property being specified twice. Once in the DSN definition, and once in the connection string. I believe Trusted Authentication will win any tie in that case. |
|
|
|
Mr Fett
Starting Member
28 Posts |
Posted - 2008-08-14 : 09:17:47
|
quote:
Originally posted by jsmith8858
You are using a DSN. Either change the definition in your DSN, or don't use a DSN and specify all of the connection properties (provider, server, database, etc) in the connection string.see:http://www.connectionstrings.com/?carrier=sqlserverif necessary.- Jeffhttp://weblogs.sqlteam.com/JeffS
Hi Jeff,Just wanted to say thanks for your help - much appreciated. This did indeed solve my problem and now all of my websites are much more secure because of it (read/write access only given where required and all input santized - mmmmm - security :-)All the bestBob |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Unable to use SQL Auth without Giving IUSR Rights