REPOST: One Web Service updates SQL, the other can't
From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/28/04
- Next message: Aaron [SQL Server MVP]: "Re: performance of 2 queries"
- Previous message: David: "performance of 2 queries"
- Next in thread: Val Mazur: "Re: REPOST: One Web Service updates SQL, the other can't"
- Reply: Val Mazur: "Re: REPOST: One Web Service updates SQL, the other can't"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 28 Oct 2004 11:01:56 -0400
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: Aaron [SQL Server MVP]: "Re: performance of 2 queries"
- Previous message: David: "performance of 2 queries"
- Next in thread: Val Mazur: "Re: REPOST: One Web Service updates SQL, the other can't"
- Reply: Val Mazur: "Re: REPOST: One Web Service updates SQL, the other can't"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
