Re: REPOST: One Web Service updates SQL, the other can't
From: Val Mazur (group51a_at_hotmail.com)
Date: 10/30/04
- Next message: Val Mazur: "Re: Updating a disconnected hiearchical recordset"
- Previous message: Val Mazur: "Re: Copy Recordset"
- In reply to: Steve Ricketts: "REPOST: One Web Service updates SQL, the other can't"
- Next in thread: Steve Ricketts: "Re: REPOST: One Web Service updates SQL, the other can't"
- Reply: Steve Ricketts: "Re: REPOST: One Web Service updates SQL, the other can't"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 21:31:27 -0400
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
--
Val Mazur
Microsoft MVP
"Steve Ricketts" <steve@velocedge.com> wrote in message
news:uhjYY7PvEHA.592@TK2MSFTNGP15.phx.gbl...
>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.
>
>
> --
> 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.
>>
>> --
>> 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:%23pVLgH5uEHA.3948@TK2MSFTNGP15.phx.gbl...
>> I'm trying.... but I just can't see anything about a Security Audio on
>> the
>> Events tab. In the tree I have:
>>
>> Cursors
>> Errors & Warning
>> Locks
>> Misc.
>> Objects
>> Scans
>> SQL Operators
>> Stored Procedures
>> Transactions
>> TSQL
>>
>> On the ADO.Net... I guess its because this is my first Web Service and I
>> just used the access methods I knew. Until now (2 years) it hasn't been
>> a
>> problem. This is the first time we've tried to run two instances of the
>> same Web Service. I'm not sure how to answer your question on shared
>> connections across all instances, so let me answer this way. When the
>> Web
>> Service starts, it opens the database connection. All subsequent calls
>> to
>> that service are handled through that connection. Its open as long as
>> the
>> Web Service is running. The second Web Service is exactly the same, only
> it
>> 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?
>> >
>> > --
>> > 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:%23X%231Ee4uEHA.3624@TK2MSFTNGP09.phx.gbl...
>> > I'm not sure how to trap the login and out events... is that "Connect"
> 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
> if
>> > > 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?
>> > >
>> > > --
>> > > 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:uKcyPM4uEHA.1860@TK2MSFTNGP15.phx.gbl...
>> > > I set the trace for the problem database and am receiving exactly
>> > > what
> I
>> > > 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
> if
>> > 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.
>> > > >
>> > > > --
>> > > > 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:uw%23Fp63uEHA.2172@TK2MSFTNGP14.phx.gbl...
>> > > > I'm sending raw SQL and should have told you that I was opening the
>> > > 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.
>> > > > >
>> > > > > --
>> > > > > 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:eZ5PcW3uEHA.3828@TK2MSFTNGP12.phx.gbl...
>> > > > > I have a production and development system both running on one PC
>> > 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.
>>
>> --
>> 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:OQlDzVCvEHA.4076@TK2MSFTNGP14.phx.gbl...
>> I posted this on .sqlserver.security under the same name and Tom Moreau
>> suggested I post here and see if you all have any ideas.
>>
>> 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!
>>
>> Thanks,
>>
>> Steve Ricketts
>>
>>
>
>
>
- Next message: Val Mazur: "Re: Updating a disconnected hiearchical recordset"
- Previous message: Val Mazur: "Re: Copy Recordset"
- In reply to: Steve Ricketts: "REPOST: One Web Service updates SQL, the other can't"
- Next in thread: Steve Ricketts: "Re: REPOST: One Web Service updates SQL, the other can't"
- Reply: Steve Ricketts: "Re: REPOST: One Web Service updates SQL, the other can't"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
