REPOST: One Web Service updates SQL, the other can't

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve Ricketts - 28 Oct 2004 16:01 GMT

I posted this on .sqlserver.security under the same subject and Tom Moreau
suggested I post here and see if you all have any ideas. The dialog between
Tom and I is posted below.

I have a production and development system both running on one PC under W2K
with SQL7sp4. One Web Service can access and update the database via ADO
fine. Another instance of the same Web Service code, accessing a different,
but identical database, can read but not update records. It seems like
whichever Web Service I start first is allowed to read and write, but the
other has only read permissions.

I'm logging in as "sa" to try and remove as many permission issues as
possible using the following connection string:

strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
Password=password"

Watching both the debugger and the trace, SQL is receiving what I send and
returning data from all "select" statements. All goes well until I send an
update statement. There is basically nothing in the code from the last
"select" call to this "update". When the "update" was sent, I trapped the
error and err.description says:

"an exception of type: (System.Runtime.InteropServices.COMException)
occurred"

And then I start getting the database closed errors on subsequent calls. I
open the database again after the error and all is fine until the next
"update" statement.

Is there a exclusive, read-only, permissions setting that I've missed
somewhere? The problem is simply the second Web Service can't write to SQL,
but why is the big question. Any help would be greatly appreciated, I'm
into days on this one!

Steve Ricketts

From public.sqlserver.security

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:uC$MeO5uEHA.4028@TK2MSFTNGP15.phx.gbl...

I'm not too sure what to suggest next. The code here doesn't show any
retrieval of the rows from the SELECT, which can be problematic. Also, it's
not a good idea to do a one-size-fits-all on statements that do and do not
return rows. For example, an UPDATE never returns rows. If you want to
stick with executing things through the Connection object - instead of using
a Command object - consider the following two approaches:

' returns a Recordset
Set rs = Cnxn.Execute ("select * from MyTable",, adCmdText)

' returns no Recordset
Cnxn.Execute ("update MyTable set x = 3 where y = 2",, adExecuteNoRecords)

Specifying the command type, as shown above, can also help performance,
since ADO doesn't have to spend time guessing.

If this doesn't help, you may want to post this in the ADO newsgroup. Val
and the guys there may be able to help.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Ricketts" <steve@velocedge.com> wrote in message
news:e%23jSlz5uEHA.3416@TK2MSFTNGP09.phx.gbl...
The code doesn't seem to be much special, but I'm happy to include the parts
that are failing.

Public strCnxn as string

strCnxn = strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN;
Data
Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
Cnxn = New ADODB.Connection()
Cnxn.Open(strCnxn)
.
.
In a procedure
Dim recData As ADODB.Recordset
Dim recCMI As ADODB.Recordset

SQL = "select * from cmi_student "
SQL = SQL & " where person_int = " & CMI(_person_int)
Call sqlExecute(recData, SQL) 'This works
If recData.EOF Then
Return False
Else
Return True
End If

SQL = "update cmi set "
SQL = SQL & " revised = " & Delimit & Now & Delimit
SQL = SQL & " where person_int = " & CMI(_person_int)
SQL = SQL & " and course_int = " & CMI(_course_int)
SQL = SQL & " and lesson_int = " & CMI(_lesson_int)
Call sqlExecute(recCMI, SQL) 'This fails
.
.
.
Function sqlExecute(ByRef RS As ADODB.Recordset, ByVal SQL As String) As
Boolean
Dim i As Integer

i = 0
tryAgain:
On Error GoTo gotError

RS = Cnxn.Execute(SQL)
If RS.EOF Then sqlExecute = False Else sqlExecute = True
Exit Function

gotError:
i = i + 1
If i > 5 Then Exit Function
Cnxn.Close()
Cnxn.Open(strCnxn)
GoTo tryagain
End Function

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:uC$MeO5uEHA.4028@TK2MSFTNGP15.phx.gbl...

> Sorry about that. Right now, I have access only to a SQL2K box, which
> obviously has more objects that it can profile than does SQL7.
>
> I'm wondering if there is some use of disconnected recordsets going on

here.

> I know ADO supports that. It's just that you have to reconnect when you
> want to update.
>
> Do you have a small snippet of your client code that we can see? I don't
> have any Interop experience but at least we can see if the code looks OK.

> opens a connection to a different database with identical structure as the
> first.
>
> Steve
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:u$QUwj4uEHA.1264@TK2MSFTNGP12.phx.gbl...
> > In the profiler, in the Events tab, click on Security Audit. In that
> tree,
> > you'll see Audit Login, Audit Login Failed, Audit Logout, Audit Object
> > Permission Event. See what that gives you in the trace.
> >
> > The connect string looks innocent enough.
> >
> > Since you're using Interop, I'm wondering if there is something there.
> > Since this is a web service, why aren't you using ADO.NET? Also, is

this

> a
> > shared connection across all instances?

and

> > "Disconnect" in the trace window? I couldn't find them in the Events
> > section of the Trace Properties dialog. The connection string is:
> >
> > strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
> > Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
> > Password=password"
> >
> > I've tried it with and without the "Network Library" parameter and
> obviously
> > there is a real IP address in the Data Source. Watching both the

debugger

> > and the trace, all goes well until the update statement I mentioned
> earlier.
> > There is basically nothing in the code from the last "select" call to

this

> > "update". It just sets up the SQL string. When I let the
> cnxn.execute(sql)
> > statement run with the "update", I trapped the error and err.description
> > says:
> >
> > "an exception of type: (System.Runtime.InteropServices.COMException)
> > occurred"
> >
> > And then I start getting the database closed errors on subsequent calls.
> I
> > open the database again after the error and all is fine until the next
> > "update" statement.
> >
> > sr
> >
> > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > news:OslYpR4uEHA.1984@TK2MSFTNGP14.phx.gbl...
> > > The error suggests that somehow the connection to SQL Server is being
> > > closed. Audit the Login and Logout events with the profiler and see

> > > that's the case. I'm wondering also if there is a connection pooling
> > issue
> > > here. Could we have a look at your connection string?

> > > sent. (I'm using sa just to eliminate as much of the permissions
> problems
> > > as possible). I'm stepping through the Visual Studio .Net debugger

and

> > it's
> > > showing me the returned data record when its a "select" but as soon as
> an
> > > "update" is issued, I get "Operation is not allowed when the object is
> > > closed". The SQL Trace shows:
> > >
> > > update cmi set revised = '10/26/2004 12:32:30 PM' where person_int =
> 23720
> > > and course_int = 645 and lesson_int = 2
> > >
> > > Which is what I sent from the web service... and why would this work

> > it's
> > > the first Web Service started and the other Service fails. It doesn't
> > seem
> > > to matter which database I use, the one I start first works and the
> second
> > > one doesn't.
> > >
> > > Steve
> > >
> > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > > news:eMxsH%233uEHA.1260@TK2MSFTNGP12.phx.gbl...
> > > > If it's connecting as sa - which, BTW, is a bad thing - then the
> > security
> > > is
> > > > essentially bypassed. The next thing I'd do is run a profiler trace
> and
> > > try
> > > > and see what's coming at your server. Hopefully, you can run the

web

> > > > service in debug mode to step through it.
> > > >
> > > database
> > > > as "sa". db_denydatawriter for sa is not checked. Does that help?
> > > >
> > > > sr
> > > >
> > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > > > news:uichWz3uEHA.2520@TK2MSFTNGP15.phx.gbl...
> > > > > Assuming that you're using raw T-SQL - not stored procs - I'd look
> at
> > > who
> > > > is
> > > > > a member of the db_denydatawriter role in the problem database.
> > Another
> > > > > thing to look at is the permissions for that user in EM. That may
> > tell
> > > > you
> > > > > if there are any explicit DENY's.
> > > > >
> > under
> > > > W2K
> > > > > with SQL7sp4. One Web Service can access and update the SQL

Server

> 7
> > > > > database fine. Another instance of the same Web Service code,
> > accessing
> > > a
> > > > > different, but identical database, can read but not update

records.

> > It
> > > > > seems like whichever Web Service I start first is allowed to read
> and
> > > > write,
> > > > > but the other has only read permissions.
> > > > >
> > > > > Is there a exclusive, read-only, permissions setting that I've
> missed
> > > > > somewhere? The problem is simply the second Web Service can't

write

> > to
> > > > SQL,
> > > > > but why is the big question. Any help would be greatly
> appreciated,
> > > I'm
> > > > > into days on this one!
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Steve Ricketts

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23nP0J$CvEHA.2508@TK2MSFTNGP10.phx.gbl...

> You may want to post your ADO code plus my comments.

W2K

> with SQL7sp4. One Web Service can access and update the database via ADO
> fine. Another instance of the same Web Service code, accessing a

different,

> but identical database, can read but not update records. It seems like
> whichever Web Service I start first is allowed to read and write, but the
> other has only read permissions.
>
> I'm logging in as "sa" to try and remove as many permission issues as
> possible using the following connection string:
>
> strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
> Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
> Password=password"
>
> Watching both the debugger and the trace, SQL is receiving what I send and
> returning data from all "select" statements. All goes well until I send

> update statement. There is basically nothing in the code from the last
> "select" call to this "update". When the "update" was sent, I trapped the
> error and err.description says:
>
> "an exception of type: (System.Runtime.InteropServices.COMException)
> occurred"
>
> And then I start getting the database closed errors on subsequent calls.

> open the database again after the error and all is fine until the next
> "update" statement.
>
> Is there a exclusive, read-only, permissions setting that I've missed
> somewhere? The problem is simply the second Web Service can't write to

SQL,

> but why is the big question. Any help would be greatly appreciated, I'm
> into days on this one!
>
> Thanks,
>
> Steve Ricketts

Reply to this Message

Val Mazur - 30 Oct 2004 02:31 GMT

Hi Steve,

I do not think it should restrict you to run another instance of the Web
Service in this case. Looks like there is something wrong in a COM side of
the application. Is it out-of-process COM? Are you using transactions inside
of the COM? I think best way to figure out what could be wrong here is to
place some sort of logging functionality into COM to see where exactly it
fails and what is the actual error message.

I would suspect that RS = Cnxn.Execute(SQL) of the sqlExecute causes
problem. First of all when you call UPDATE statement, you cannot expect to
get recordset back and ADO could generate error here. But even if you are
getting recordset back, you suppose to use Set instruction to set reference
to the recordset, like in next code

Set RS = Cnxn.Execute(SQL)

I would suggest to separate opening of the recordset and executing of the
action queries functionality

Signature

Val Mazur
Microsoft MVP

>I posted this on .sqlserver.security under the same subject and Tom Moreau
> suggested I post here and see if you all have any ideas. The dialog
[quoted text clipped - 358 lines]
>>
>> Steve Ricketts

Reply to this Message

Steve Ricketts - 30 Oct 2004 14:17 GMT

That was it and so obvious, I'm embarassed!! Of course when the update or
insert is executed a reference to rs.eof is invalid and the program bombs.
Stupid mistake on my part. On the "set" reference, in Visual Basic .Net set
is no longer allowed so set rs = cnxn.execute(sql) is the only way to
execute the statement.

Thanks to you and Tom for all your help.

Steve

> Hi Steve,
>
[quoted text clipped - 378 lines]
> >>
> >> Steve Ricketts

Reply to this Message

VB Forum / Database Access / October 2004

REPOST: One Web Service updates SQL, the other can't