Re: One Web Service updates SQL, another can't?

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 15:39:21 -0400

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

Relevant Pages

  • Re: One Web Service updates SQL, another cant?
    ... I guess its because this is my first Web Service and I ... it opens the database connection. ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.security)
  • Re: One Web Service updates SQL, another cant?
    ... > ' returns no Recordset ... > Columnist, SQL Server Professional ... it opens the database connection. ... >> Web Service is running. ...
    (microsoft.public.sqlserver.security)
  • Re: One Web Service updates SQL, another cant?
    ... > ' returns no Recordset ... > Columnist, SQL Server Professional ... it opens the database connection. ... >> Web Service is running. ...
    (microsoft.public.sqlserver.security)
  • Re: One Web Service updates SQL, another cant?
    ... > Columnist, SQL Server Professional ... I guess its because this is my first Web Service and I ... it opens the database connection. ... >> In the profiler, in the Events tab, click on Security Audit. ...
    (microsoft.public.sqlserver.security)
  • Login failed for user sa over and over again
    ... I have a SQL Server 2K with ver. ... I checked event viewer and trace the activity using the sql ... How can i prevent this and how to determine the source of this connection ...
    (microsoft.public.sqlserver.server)