Re: One Web Service updates SQL, another can't?
From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/26/04
- Next message: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Previous message: Tim S: "RE: SSL won't work under Domain User account. MS Fix buletin does not"
- In reply to: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Reply: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 26 Oct 2004 15:29:09 -0400
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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Previous message: Tim S: "RE: SSL won't work under Domain User account. MS Fix buletin does not"
- In reply to: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Reply: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
