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 14:23:05 -0400

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

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
    (microsoft.public.sqlserver.security)
  • Re: Security question ..
    ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
    (microsoft.public.sqlserver.server)