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 13:50:58 -0400

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: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: One Web Service updates SQL, another cant?
    ... Columnist, SQL Server Professional ... I guess its because this is my first Web Service and I> just used the access methods I knew. ... it opens the database connection. ... The second Web Service is exactly the same, only it> opens a connection to a different database with identical structure as the> first. ...
    (microsoft.public.sqlserver.security)
  • Re: (Newbie)Application Roles
    ... level: the database itself. ... SQL Server accommodates these needs through the use of application ... the user's connection through a specific application. ... the connection permanently loses all permissions applied to ...
    (microsoft.public.sqlserver.security)
  • Re: Help! Set up Windows Group to access application
    ... The connection string for a trusted connection is: ... --add login as database user ... SQL Server MVP ... If you have troubles with finding your ways in Crystal Reports, ...
    (microsoft.public.sqlserver.security)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)