SQL Adding a User account: connections

Showing posts with label connections. Show all posts

Sunday, March 25, 2012

Adverse effects of too may "sleeping" connections

Hi,
I have an application which maintains around 25-30 connections to the
database.
The status for all those are "sleeping" most of the time.
The database is MSDE.
I would like to know what are the effects on performance, locking and any
other issuses due to this "sleeping" connections.
Any technical document or link would be of great help
hi,
LazyDBA wrote:
> Hi,
> I have an application which maintains around 25-30 connections to the
> database.
> The status for all those are "sleeping" most of the time.
> The database is MSDE.
> I would like to know what are the effects on performance, locking and
> any other issuses due to this "sleeping" connections.
> Any technical document or link would be of great help
no effects on on performance and/or locking... just memory footprint and
"wasted" resources...
each connection, live or sleeping, will eat about 24kb of memory, calculated
as 12 KB + 3 times the Network Packet Size (default setting that can be
partially customized via sp_configure system stored procedure modifying the
'user connections' setting), used to store the data structures holding the
connection 's context, as long as for buffer used to send and receive the
relative associated network streams (default to 4KB network packet setting),
that can be stolen from the buffer pool memory region and/or the MemToLeave
memory area... as these memory regions are not infinite (:D) you are wasting
some bytes.. :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Thursday, March 8, 2012

aduit connections to SQL Server

On sql 2005 is there any audit logs on by default that log connections to sq
l
server and what database the connection was to.
If not on, is there one that you can turn on.
Not to long ago we had some records deleted out of the sql database
mysteriously. I am trying to find out how that happened and to keep it from
happening again.There is a default trace in SQL Server 2005 that is enabled
by default. You can find information about the default trace
in books online and this link has more info:
http://www.mssqltips.com/tip.asp?tip=1111
-Sue
On Wed, 14 Feb 2007 08:49:05 -0800, Andrew
<Andrew@.discussions.microsoft.com> wrote:

>On sql 2005 is there any audit logs on by default that log connections to s
ql
>server and what database the connection was to.
>If not on, is there one that you can turn on.
>Not to long ago we had some records deleted out of the sql database
>mysteriously. I am trying to find out how that happened and to keep it from
>happening again.|||Andrew
In addition on Sue's reply
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
You can use
1) DDL Triggers
2) Event notifications
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:8B1D1BB3-9EC2-4C31-87FA-064A1FB5C2E0@.microsoft.com...
> On sql 2005 is there any audit logs on by default that log connections to
> sql
> server and what database the connection was to.
> If not on, is there one that you can turn on.
> Not to long ago we had some records deleted out of the sql database
> mysteriously. I am trying to find out how that happened and to keep it
> from
> happening again.
>

Saturday, February 25, 2012

adodb connections to a linked server

Hello,
I'd like to create an ado connection to my linked server called Prob_Schd on
a machine called pccn-nt.
When I use the code below I recieve an error of the type ' Invalid
connection string attribute'. Would someone show me how to create an ado
connection to a linked server?
Thanks,
Chieko
dim oConn, strConn
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial Catalog=null
trusted_Connection=yes "
oConn.Open strConn
set GetDataConnection = oConnADO doesn't know anything about SQL Server's linked servers. You could
connect locally, and then execute queries like adoConnObject.Execute("SELECT
* FROM Prob_Schd.pubs.dbo.authors") or, you could make an OLEDB connection
directly to the linked server.
The specific error you are getting is because the "Datasource" attribute
you've used should have a space, "Data Source"... but I'm pretty sure ADO
will still be unable to find that server unless it is in the hosts file on
the machine (in other words, it doesn't care if that's a linked server in
SQL Server).
"Chieko Kuroda" <ckuroda@.med.unc.edu> wrote in message
news:O2OVMHuzDHA.3520@.tk2msftngp13.phx.gbl...

quote:

> Hello,
> I'd like to create an ado connection to my linked server called Prob_Schd

quote:

> a machine called pccn-nt.
> When I use the code below I recieve an error of the type ' Invalid
> connection string attribute'. Would someone show me how to create an ado
> connection to a linked server?
> Thanks,
> Chieko
>
> dim oConn, strConn
> Set oConn = Server.CreateObject("ADODB.Connection")
> strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial

Catalog=null

quote:

> trusted_Connection=yes "
> oConn.Open strConn
> set GetDataConnection = oConn
>

Thursday, February 16, 2012

ADO Connections - close them or keep them open?

I surely don't want to start a religious debate here...
I'm curious - what is the conventional wisdom about using a single global connection in a VB frontend (using ADO) and leaving that connection open for the entire time the app is running?
I've seen people suggest that you close the connection and re-open for every query.
The specifics of my app is 1000+ users - dozens stay in all day long - others stay in for a short time (less than 30 minutes). Large WAN - dozens of buildings across town.
Thanks in advance.My $.02 It depends on your licensing model and what you mean by stay in all
day. Do they actually run the application or just have it open. If it is
open and idle I believe Windows 2000 SP2 cuts idle connections after 20
minutes. (SP2 fixed a bug hat allowed unlimited open connection time on idle
connections)
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> I surely don't want to start a religious debate here...
> I'm curious - what is the conventional wisdom about using a single global
connection in a VB frontend (using ADO) and leaving that connection open for
the entire time the app is running?
> I've seen people suggest that you close the connection and re-open for
every query.
> The specifics of my app is 1000+ users - dozens stay in all day long -
others stay in for a short time (less than 30 minutes). Large WAN - dozens
of buildings across town.
> Thanks in advance.|||close it, set it to nothing and let connection pooling do its thing.
Cheers
Greg Jackson
PDX, Oregon|||My approach is ALWAYS get in, tweak the data, get out. Don't hold a
connection open longer than you have to.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> I surely don't want to start a religious debate here...
> I'm curious - what is the conventional wisdom about using a single global
connection in a VB frontend (using ADO) and leaving that connection open for
the entire time the app is running?
> I've seen people suggest that you close the connection and re-open for
every query.
> The specifics of my app is 1000+ users - dozens stay in all day long -
others stay in for a short time (less than 30 minutes). Large WAN - dozens
of buildings across town.
> Thanks in advance.|||Why not hold a connection open? Why cause re-authentication of the user to the DB?
If someone opens a maintenance form and starts calling up records - when would you decide they were done enough to close the connection?
What is the actual downside of a PC holding a connection open to the DB for lets say - oh - 4 hours?
"Aaron [SQL Server MVP]" wrote:
> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> > I surely don't want to start a religious debate here...
> >
> > I'm curious - what is the conventional wisdom about using a single global
> connection in a VB frontend (using ADO) and leaving that connection open for
> the entire time the app is running?
> >
> > I've seen people suggest that you close the connection and re-open for
> every query.
> >
> > The specifics of my app is 1000+ users - dozens stay in all day long -
> others stay in for a short time (less than 30 minutes). Large WAN - dozens
> of buildings across town.
> >
> > Thanks in advance.
>
>|||What is wrong with holding a connection open?
What is the cost of re-authenticating the user to the DB?
What is the actual downside of hold the connection - let's say - for 4 or 5 hours?
If a user goes into a maintenance form and starts working on a record to change it - what would be the moment you all thought that the connection is worth closing?
"Aaron [SQL Server MVP]" wrote:
> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> > I surely don't want to start a religious debate here...
> >
> > I'm curious - what is the conventional wisdom about using a single global
> connection in a VB frontend (using ADO) and leaving that connection open for
> the entire time the app is running?
> >
> > I've seen people suggest that you close the connection and re-open for
> every query.
> >
> > The specifics of my app is 1000+ users - dozens stay in all day long -
> others stay in for a short time (less than 30 minutes). Large WAN - dozens
> of buildings across town.
> >
> > Thanks in advance.
>
>|||What is wrong with holding a connection open?
What is the cost of re-authenticating the user to the DB?
What is the actual downside of hold the connection - let's say - for 4 or 5 hours?
If a user goes into a maintenance form and starts working on a record to change it - what would be the moment you all thought that the connection is worth closing?
"Aaron [SQL Server MVP]" wrote:
> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> > I surely don't want to start a religious debate here...
> >
> > I'm curious - what is the conventional wisdom about using a single global
> connection in a VB frontend (using ADO) and leaving that connection open for
> the entire time the app is running?
> >
> > I've seen people suggest that you close the connection and re-open for
> every query.
> >
> > The specifics of my app is 1000+ users - dozens stay in all day long -
> others stay in for a short time (less than 30 minutes). Large WAN - dozens
> of buildings across town.
> >
> > Thanks in advance.
>
>|||how do you share that connection between users ?
you are going to have to manage your own "pool" technically.
Keeping a global connection around is kind of an old mentality than in
today's environment does not make sense.
In short, your application will not scale at all if you attempt to do this.
Connection pooling built into OLE\DB pools connections so that the
authentication is not performed with each use of a shared connection.
trust me. you are trying to do manually, what will be done better for you
Automatically.
This is a well published "best practice" or "pattern" for data access in
Enterprise applications.
hope this helps, if you want more details other then "Because I said So"
just let me know and I'll point you to white papers galore (Starting with
"Data Access Best Practices" docs)
Cheers,
Greg J
PDX, Oregon|||Jaxon,
Every white paper I read is about WEB Access and ASP pages.
This is not what we have here. We have a school district - on an internal network - many buildings - 1000+ teachers. Teachers get into attendance app for less than 30 minutes and post attendance for classes. Admin folks and secretaries get in for the whole day - call up kids, start maintenance - get phone calls - interruptions.
What do you mean "share the connection". I meant "global connection" in VB - global to all the forms that the user might call up in the app - I didn't mean global to all users.
Each workstation runs the app and makes a connection - no sharing involved. We do all our server side work with SPROCS - so ADO calls the SPROC from VB - returns the recordset and then the connection stays open until the next ADO/SPROC call.
We use win-nt authentication - so taking advantage of SQL pooling of connections so a similiar one can be found doesn't really make sense to us.
I've found info that says that establishing the connection can be time consuming - so why relinquish it and re-establish it'
Other programmers at this school district use ACCESS to touch there DB's. I see that ACCESS opens "several" connections for just one user at a time - this seems more of a waste of resources.
"Jaxon" wrote:
> how do you share that connection between users ?
> you are going to have to manage your own "pool" technically.
> Keeping a global connection around is kind of an old mentality than in
> today's environment does not make sense.
> In short, your application will not scale at all if you attempt to do this.
> Connection pooling built into OLE\DB pools connections so that the
> authentication is not performed with each use of a shared connection.
>
> trust me. you are trying to do manually, what will be done better for you
> Automatically.
>
> This is a well published "best practice" or "pattern" for data access in
> Enterprise applications.
> hope this helps, if you want more details other then "Because I said So"
> just let me know and I'll point you to white papers galore (Starting with
> "Data Access Best Practices" docs)
>
> Cheers,
> Greg J
> PDX, Oregon
>
>|||see inline:
> Every white paper I read is about WEB Access and ASP pages.
There are many white papers on Data Access Strategies and best practices for
N-Tier Applications. It is essentially the same for Web Apps. Web apps can
be (and likely should be) designed as n-tier apps.
> This is not what we have here. We have a school district - on an internal
network - many buildings - 1000+ teachers. Teachers get into attendance app
for less than 30 minutes and post attendance for classes. Admin folks and
secretaries get in for the whole day - call up kids, start maintenance - get
phone calls - interruptions.
That's fine. This still works for N-Tier Architecture
> What do you mean "share the connection". I meant "global connection" in
VB - global to all the forms that the user might call up in the app - I
didn't mean global to all users.
if Each user keeps a Global Connection object in their App on the client
side, you likely will run out of connections quickly and requests will start
to queue up. This is not scalable as larger number of clients adds up.
Connections are a precious resource that should be obtained late and
released early so that connections dont become your system bottleneck.
> Each workstation runs the app and makes a connection - no sharing
involved. We do all our server side work with SPROCS - so ADO calls the
SPROC from VB - returns the recordset and then the connection stays open
until the next ADO/SPROC call.
This sounds like a class 2-tier or Client Server app to me then. likely too
late to turn it into an N-Tier App...you may be stuck. However, I would
still not keep global connections around.
> We use win-nt authentication - so taking advantage of SQL pooling of
connections so a similiar one can be found doesn't really make sense to us.
if each user logs in directly to sql server, you are rigth. You might want
to rethink this and have your code access the DB using a handful of standard
logins (ReadOnly, Read Write, REad Write Delete, Admin, Etc). Then you can
take advantage of pooling and you can scale better
> I've found info that says that establishing the connection can be time
consuming - so why relinquish it and re-establish it'
You're right. You dont want to do this. You want to try to pool connections.
> Other programmers at this school district use ACCESS to touch there DB's.
I see that ACCESS opens "several" connections for just one user at a time -
this seems more of a waste of resources.
Yes Access connectivity to a SQL Server DB can be downright crippling.
hope all this info helps. Sounds like you are already married to an
architecture that you wont be able to change. You may be stuck into doing
what you are currently doing...
Cheers
GAJ|||Thanks greatly for the info...
It would not be impossible for us to set a timeout on the connection and simply re-establish if it's not open. We probably have only 3 or 4 sub's that actually request data from the SPROCS on the server. If we start to see a problem with connection issues - we can experiment with that.
Whenever I go into CURRENT ACTIVITY it appears that the ACCESS clients are using 4 times more connections to there DB's than we are to our DB - but we aren't going to fully roll this out till next September.
One last question - what have you read or what experience do you have with CONNECTION maximums in SQL?
"Jaxon" wrote:
> see inline:
> > Every white paper I read is about WEB Access and ASP pages.
> There are many white papers on Data Access Strategies and best practices for
> N-Tier Applications. It is essentially the same for Web Apps. Web apps can
> be (and likely should be) designed as n-tier apps.
> >
> > This is not what we have here. We have a school district - on an internal
> network - many buildings - 1000+ teachers. Teachers get into attendance app
> for less than 30 minutes and post attendance for classes. Admin folks and
> secretaries get in for the whole day - call up kids, start maintenance - get
> phone calls - interruptions.
> That's fine. This still works for N-Tier Architecture
> >
> > What do you mean "share the connection". I meant "global connection" in
> VB - global to all the forms that the user might call up in the app - I
> didn't mean global to all users.
> if Each user keeps a Global Connection object in their App on the client
> side, you likely will run out of connections quickly and requests will start
> to queue up. This is not scalable as larger number of clients adds up.
> Connections are a precious resource that should be obtained late and
> released early so that connections dont become your system bottleneck.
> >
> > Each workstation runs the app and makes a connection - no sharing
> involved. We do all our server side work with SPROCS - so ADO calls the
> SPROC from VB - returns the recordset and then the connection stays open
> until the next ADO/SPROC call.
> This sounds like a class 2-tier or Client Server app to me then. likely too
> late to turn it into an N-Tier App...you may be stuck. However, I would
> still not keep global connections around.
> >
> > We use win-nt authentication - so taking advantage of SQL pooling of
> connections so a similiar one can be found doesn't really make sense to us.
> if each user logs in directly to sql server, you are rigth. You might want
> to rethink this and have your code access the DB using a handful of standard
> logins (ReadOnly, Read Write, REad Write Delete, Admin, Etc). Then you can
> take advantage of pooling and you can scale better
> >
> > I've found info that says that establishing the connection can be time
> consuming - so why relinquish it and re-establish it'
> You're right. You dont want to do this. You want to try to pool connections.
> >
> > Other programmers at this school district use ACCESS to touch there DB's.
> I see that ACCESS opens "several" connections for just one user at a time -
> this seems more of a waste of resources.
> Yes Access connectivity to a SQL Server DB can be downright crippling.
> hope all this info helps. Sounds like you are already married to an
> architecture that you wont be able to change. You may be stuck into doing
> what you are currently doing...
>
> Cheers
>
> GAJ
>
>|||in line:
> It would not be impossible for us to set a timeout on the connection and
simply re-establish if it's not open. We probably have only 3 or 4 sub's
that actually request data from the SPROCS on the server. If we start to
see a problem with connection issues - we can experiment with that.
That is one idea (timeout stuff). Since only a few subs actually hit the db,
you *Could* have those subs call a com component that handles are your data
access. Then the COM component could manage all the connection stuff for
you. Since everyone goes through the COM object for DB Access, technically
you would only need one login, and hence , connections would be pooled.
(that is classic N-Tier bread - n - butter) food for thought....
> Whenever I go into CURRENT ACTIVITY it appears that the ACCESS clients are
using 4 times more connections to there DB's than we are to our DB - but we
aren't going to fully roll this out till next September.
>
Yeah, access as a front end sucks. It is well documented that Access eats up
connections (I dont remember why. I saw an article explaining this in the
past. I think it was written by Bob Beauchamin)
> One last question - what have you read or what experience do you have with
CONNECTION maximums in SQL?
Not sure what you are asking here ....? SQL server can handle thousands of
connections. however, each connection consumes resources on both the client
and on the server (Socket connections, memory, etc)
Enjoying the chat. Best Wishes
GAJ
> "Jaxon" wrote:
> > see inline:
> >
> > > Every white paper I read is about WEB Access and ASP pages.
> > There are many white papers on Data Access Strategies and best practices
for
> > N-Tier Applications. It is essentially the same for Web Apps. Web apps
can
> > be (and likely should be) designed as n-tier apps.
> >
> > >
> > > This is not what we have here. We have a school district - on an
internal
> > network - many buildings - 1000+ teachers. Teachers get into attendance
app
> > for less than 30 minutes and post attendance for classes. Admin folks
and
> > secretaries get in for the whole day - call up kids, start maintenance -
get
> > phone calls - interruptions.
> >
> > That's fine. This still works for N-Tier Architecture
> >
> > >
> > > What do you mean "share the connection". I meant "global connection"
in
> > VB - global to all the forms that the user might call up in the app - I
> > didn't mean global to all users.
> >
> > if Each user keeps a Global Connection object in their App on the client
> > side, you likely will run out of connections quickly and requests will
start
> > to queue up. This is not scalable as larger number of clients adds up.
> >
> > Connections are a precious resource that should be obtained late and
> > released early so that connections dont become your system bottleneck.
> >
> > >
> > > Each workstation runs the app and makes a connection - no sharing
> > involved. We do all our server side work with SPROCS - so ADO calls the
> > SPROC from VB - returns the recordset and then the connection stays open
> > until the next ADO/SPROC call.
> >
> > This sounds like a class 2-tier or Client Server app to me then. likely
too
> > late to turn it into an N-Tier App...you may be stuck. However, I would
> > still not keep global connections around.
> >
> > >
> > > We use win-nt authentication - so taking advantage of SQL pooling of
> > connections so a similiar one can be found doesn't really make sense to
us.
> >
> > if each user logs in directly to sql server, you are rigth. You might
want
> > to rethink this and have your code access the DB using a handful of
standard
> > logins (ReadOnly, Read Write, REad Write Delete, Admin, Etc). Then you
can
> > take advantage of pooling and you can scale better
> >
> > >
> > > I've found info that says that establishing the connection can be time
> > consuming - so why relinquish it and re-establish it'
> >
> > You're right. You dont want to do this. You want to try to pool
connections.
> >
> > >
> > > Other programmers at this school district use ACCESS to touch there
DB's.
> > I see that ACCESS opens "several" connections for just one user at a
time -
> > this seems more of a waste of resources.
> >
> > Yes Access connectivity to a SQL Server DB can be downright crippling.
> >
> > hope all this info helps. Sounds like you are already married to an
> > architecture that you wont be able to change. You may be stuck into
doing
> > what you are currently doing...
> >
> >
> > Cheers
> >
> >
> > GAJ
> >
> >
> >|||Thanks again for the info...
Here's a quick synopsis on who we are, what we do and how we decided to do it.
We do enterprise apps for municipalities, school districts - labor unions...
We came from 2 decades of mainframe Digital VAX systems - large and small - with our own proprietary RDBMS. We developed our own because we liked the ability to create "functions" in the RDBMS to perform business logic. This was a novel idea 20 years ago. If we needed to calculate if a kid got on the honor roll, we create a function called HONOR - probably developed it in ASSEMBLER - and "hooked" it into our equation parser/compiler in the RDBMS.
With the "loss" of Digital, we needed to port our stuff to PC's - MS with SQL 2000 finally had a product that could handle the systems we had - 2.5 millions rows in some tables - large scale stuff.
On the VAX we had a "general maintenance program" - we could setup maintenance forms with no programming. SCREEN files would tell the system where to draw input boxes - etc. Since it was fully integrated in our RDBMS, we could put business hooks where ever we wanted.
So, here we are 3 years into the VB w/MS SQL conversion and we have once again created a "general maintenance program" - basically a VB program that can create FORMS out of nothing - getting the x/y/size info from a table - binding to SQL SPROCS based on a pattern we developed.
We decided against 3-tier, as we wanted just this one VB APP to support schools, towns - whatever we wanted - calling SPROCS to fill TEXT BOXES with VIEW data - MS FLEX GRIDS with EDITABLE data - etc. Every FORM has the same "functionality buttons" - and as many text boxes and grids as you like - all defined in a SQL table.
The VB Client doesn't know if it's maintaining a STUDENT - a VENDOR - a DOCTOR - it's vanilla - lightweight - no business logic.
All business logic in SPROCS - in T-SQL. Basically the SPROCS are our middle tier - so in a way, it's SQL for DATA, SPROCS for business and VB thin-client to maintain it all.
It's been a very good experience so far. We even have our own report writer - much more functionality than Crystal - and once again - it's homegrown so if we have a odd quirk we need to handle, we can do it.
"Jaxon" wrote:
> in line:
> > It would not be impossible for us to set a timeout on the connection and
> simply re-establish if it's not open. We probably have only 3 or 4 sub's
> that actually request data from the SPROCS on the server. If we start to
> see a problem with connection issues - we can experiment with that.
> That is one idea (timeout stuff). Since only a few subs actually hit the db,
> you *Could* have those subs call a com component that handles are your data
> access. Then the COM component could manage all the connection stuff for
> you. Since everyone goes through the COM object for DB Access, technically
> you would only need one login, and hence , connections would be pooled.
> (that is classic N-Tier bread - n - butter) food for thought....
> >
> > Whenever I go into CURRENT ACTIVITY it appears that the ACCESS clients are
> using 4 times more connections to there DB's than we are to our DB - but we
> aren't going to fully roll this out till next September.
> >
> Yeah, access as a front end sucks. It is well documented that Access eats up
> connections (I dont remember why. I saw an article explaining this in the
> past. I think it was written by Bob Beauchamin)
> > One last question - what have you read or what experience do you have with
> CONNECTION maximums in SQL?
> Not sure what you are asking here ....? SQL server can handle thousands of
> connections. however, each connection consumes resources on both the client
> and on the server (Socket connections, memory, etc)
>
> Enjoying the chat. Best Wishes
>
> GAJ
> >
> > "Jaxon" wrote:
> >
> > > see inline:
> > >
> > > > Every white paper I read is about WEB Access and ASP pages.
> > > There are many white papers on Data Access Strategies and best practices
> for
> > > N-Tier Applications. It is essentially the same for Web Apps. Web apps
> can
> > > be (and likely should be) designed as n-tier apps.
> > >
> > > >
> > > > This is not what we have here. We have a school district - on an
> internal
> > > network - many buildings - 1000+ teachers. Teachers get into attendance
> app
> > > for less than 30 minutes and post attendance for classes. Admin folks
> and
> > > secretaries get in for the whole day - call up kids, start maintenance -
> get
> > > phone calls - interruptions.
> > >
> > > That's fine. This still works for N-Tier Architecture
> > >
> > > >
> > > > What do you mean "share the connection". I meant "global connection"
> in
> > > VB - global to all the forms that the user might call up in the app - I
> > > didn't mean global to all users.
> > >
> > > if Each user keeps a Global Connection object in their App on the client
> > > side, you likely will run out of connections quickly and requests will
> start
> > > to queue up. This is not scalable as larger number of clients adds up.
> > >
> > > Connections are a precious resource that should be obtained late and
> > > released early so that connections dont become your system bottleneck.
> > >
> > > >
> > > > Each workstation runs the app and makes a connection - no sharing
> > > involved. We do all our server side work with SPROCS - so ADO calls the
> > > SPROC from VB - returns the recordset and then the connection stays open
> > > until the next ADO/SPROC call.
> > >
> > > This sounds like a class 2-tier or Client Server app to me then. likely
> too
> > > late to turn it into an N-Tier App...you may be stuck. However, I would
> > > still not keep global connections around.
> > >
> > > >
> > > > We use win-nt authentication - so taking advantage of SQL pooling of
> > > connections so a similiar one can be found doesn't really make sense to
> us.
> > >
> > > if each user logs in directly to sql server, you are rigth. You might
> want
> > > to rethink this and have your code access the DB using a handful of
> standard
> > > logins (ReadOnly, Read Write, REad Write Delete, Admin, Etc). Then you
> can
> > > take advantage of pooling and you can scale better
> > >
> > > >
> > > > I've found info that says that establishing the connection can be time
> > > consuming - so why relinquish it and re-establish it'
> > >
> > > You're right. You dont want to do this. You want to try to pool
> connections.
> > >
> > > >
> > > > Other programmers at this school district use ACCESS to touch there
> DB's.
> > > I see that ACCESS opens "several" connections for just one user at a
> time -
> > > this seems more of a waste of resources.
> > >
> > > Yes Access connectivity to a SQL Server DB can be downright crippling.
> > >
> > > hope all this info helps. Sounds like you are already married to an
> > > architecture that you wont be able to change. You may be stuck into
> doing
> > > what you are currently doing...
> > >
> > >
> > > Cheers
> > >
> > >
> > > GAJ
> > >
> > >
> > >
>
>|||> 2.5 millions rows in some tables - large scale stuff.
LOL! That's not very "large scale" in my experience.

ADO Connections

Forgive me if this is the wrong forum, but it does have to do with data access. Just a quick question: When using an ADO Component /Object to connect to a database, C# or whatever, that connection that is established - How likely is it that data being retrieved from the database is intercepted, is that channel encrypted/secured, and if not for either questions, how do i secure that channel?

Please check following blog:

http://blogs.msdn.com/sql_protocols/archive/2005/10/04/476705.aspx

If you want to force client encryption, there is a connection string property in ADO, please see below link:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

good luck!

Ming.

Monday, February 13, 2012

ado "sql authentication" connections affected by loss of domain controller

a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.

b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.

Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.

Thanks,
Frank"Frank" <frank@.policecentral.com> wrote in message
news:c148e27c.0401120604.7d0b3c0f@.posting.google.c om...
> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank

This is a complete guess, and may be an overly obvious question, but could
the 50% failure rate be due to unsuccessful name resolution, and not an
MSSQL login issue? So in the cases where the SQL authenticated connection
fails, can you still resolve the name of the SQL server? Certainly in
theory, losing the domain controller shouldn't affect SQL logins, provided
that the client can actually find the server to begin with.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4002fd9c$1_1@.news.bluewin.ch>...
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.c om...
> > I have observed that a temporary loss of a domain controller can cause
> > problems creating new ado connections between a client machine running
> > ado and a separate sql server machine that are members of the domain.
> > I understand why this happens when creating connections with windows
> > authentication. What is a mystery is that it also sometimes effects
> > new connections that use "sql authentication". Below is a description
> > of my test scenario.
> > a. Setup 3 machines.
> > i. one domain controller machine. windows 2000 based. I have tried
> > both a regular domain setup and a domain setup in compatibility mode.
> > ii. one sql server machine that is a member of the domain. windows
> > 2000.
> > iii. one client machine running an ado test program that communicates
> > with the sql server machine. I have tried both 2000 and xp.
> > b. Start your test ado program and create a connection.
> > c. Fire a query.
> > d. It should work.
> > e. Unplug the network cord on the domain controller.
> > f. Create a new connection and fire a new connection about a minute or
> > so after.
> > g. It should work. Apparently the client caches account information
> > from the domain controller for a certain amount of time. The time
> > seems to be shorter by default in xp than 2000.
> > h. Wait 20 minutes or more.
> > i. Create another new connection. You will notice a timeout error.
> > If your using windows authentication the timeout will happen 100% of
> > the time. That is to be expected. If your using sql authentication
> > the timeout seems to happen about 50% of the time. I can't explain it
> > other than some strange Microsoft bug. I speculate that it may be
> > some bug with the way ado caches connections. Perhaps a previously
> > setup windows authentication connection gets reused by a request for a
> > sql authentication connection. However I haven't been able to prove
> > it.
> > Any insight you can offer would be appreciated. I also have a test
> > program you can use to reproduce this behavior if you are interested.
> > Thanks,
> > Frank
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon

I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.

ado "sql authentication" connections affected by loss of domain controller

I have observed that a temporary loss of a domain controller can cause
problems creating new ado connections between a client machine running
ado and a separate sql server machine that are members of the domain.
I understand why this happens when creating connections with windows
authentication. What is a mystery is that it also sometimes effects
new connections that use "sql authentication". Below is a description
of my test scenario.
a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.
b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.
Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.
Thanks,
Frank"Frank" <frank@.policecentral.com> wrote in message
news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank
This is a complete guess, and may be an overly obvious question, but could
the 50% failure rate be due to unsuccessful name resolution, and not an
MSSQL login issue? So in the cases where the SQL authenticated connection
fails, can you still resolve the name of the SQL server? Certainly in
theory, losing the domain controller shouldn't affect SQL logins, provided
that the client can actually find the server to begin with.
Simon|||Which network library are you using to establish the
connection? Named pipes will require an underlying
NetBIOS connection to the SQL Server, which may result in
the "cache-ing" behavior you are seeing. NetBIOS
connections will require NT user account authentication
and the use of a domain controller.
If you refer to the SQL Server by IP Address in your
connectino string, you can force the client to connect
using TCP/IP which does not require an underlying Named
Pipe connection.
I don't know if this will help, but it is one possible
explanation.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>I have observed that a temporary loss of a domain
controller can cause
>problems creating new ado connections between a client
machine running
>ado and a separate sql server machine that are members
of the domain.
>I understand why this happens when creating connections
with windows
>authentication. What is a mystery is that it also
sometimes effects
>new connections that use "sql authentication". Below is
a description
>of my test scenario.
>a. Setup 3 machines.
> i. one domain controller machine. windows 2000
based. I have tried
>both a regular domain setup and a domain setup in
compatibility mode.
> ii. one sql server machine that is a member of
the domain. windows
>2000.
> iii. one client machine running an ado test
program that communicates
>with the sql server machine. I have tried both 2000 and
xp.
>b. Start your test ado program and create a connection.
>c. Fire a query.
>d. It should work.
>e. Unplug the network cord on the domain controller.
>f. Create a new connection and fire a new connection
about a minute or
>so after.
>g. It should work. Apparently the client caches account
information
>from the domain controller for a certain amount of
time. The time
>seems to be shorter by default in xp than 2000.
>h. Wait 20 minutes or more.
>i. Create another new connection. You will notice a
timeout error.
>If your using windows authentication the timeout will
happen 100% of
>the time. That is to be expected. If your using sql
authentication
>the timeout seems to happen about 50% of the time. I
can't explain it
>other than some strange Microsoft bug. I speculate that
it may be
>some bug with the way ado caches connections. Perhaps a
previously
>setup windows authentication connection gets reused by a
request for a
>sql authentication connection. However I haven't been
able to prove
>it.
>Any insight you can offer would be appreciated. I also
have a test
>program you can use to reproduce this behavior if you
are interested.
>Thanks,
>Frank
>.
>|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4002fd9c$1_1@.news.bluewin.ch>...
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> > I have observed that a temporary loss of a domain controller can cause
> > problems creating new ado connections between a client machine running
> > ado and a separate sql server machine that are members of the domain.
> > I understand why this happens when creating connections with windows
> > authentication. What is a mystery is that it also sometimes effects
> > new connections that use "sql authentication". Below is a description
> > of my test scenario.
> >
> > a. Setup 3 machines.
> > i. one domain controller machine. windows 2000 based. I have tried
> > both a regular domain setup and a domain setup in compatibility mode.
> > ii. one sql server machine that is a member of the domain. windows
> > 2000.
> > iii. one client machine running an ado test program that communicates
> > with the sql server machine. I have tried both 2000 and xp.
> >
> > b. Start your test ado program and create a connection.
> > c. Fire a query.
> > d. It should work.
> > e. Unplug the network cord on the domain controller.
> > f. Create a new connection and fire a new connection about a minute or
> > so after.
> > g. It should work. Apparently the client caches account information
> > from the domain controller for a certain amount of time. The time
> > seems to be shorter by default in xp than 2000.
> > h. Wait 20 minutes or more.
> > i. Create another new connection. You will notice a timeout error.
> > If your using windows authentication the timeout will happen 100% of
> > the time. That is to be expected. If your using sql authentication
> > the timeout seems to happen about 50% of the time. I can't explain it
> > other than some strange Microsoft bug. I speculate that it may be
> > some bug with the way ado caches connections. Perhaps a previously
> > setup windows authentication connection gets reused by a request for a
> > sql authentication connection. However I haven't been able to prove
> > it.
> >
> > Any insight you can offer would be appreciated. I also have a test
> > program you can use to reproduce this behavior if you are interested.
> >
> > Thanks,
> > Frank
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon
I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.|||I considered that possibility. I did bring up the "Client Network
Utility" on the client and made tcp/ip the only choice. The behavior
did not change. However I am referencing the machine by name and not
ip. I will try the ip technique and report back.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> Which network library are you using to establish the
> connection? Named pipes will require an underlying
> NetBIOS connection to the SQL Server, which may result in
> the "cache-ing" behavior you are seeing. NetBIOS
> connections will require NT user account authentication
> and the use of a domain controller.
> If you refer to the SQL Server by IP Address in your
> connectino string, you can force the client to connect
> using TCP/IP which does not require an underlying Named
> Pipe connection.
> I don't know if this will help, but it is one possible
> explanation.
> Matthew Bando
> BandoM@.CSCTGI(remove this).com
>
> >--Original Message--
> >I have observed that a temporary loss of a domain
> controller can cause
> >problems creating new ado connections between a client
> machine running
> >ado and a separate sql server machine that are members
> of the domain.
> >I understand why this happens when creating connections
> with windows
> >authentication. What is a mystery is that it also
> sometimes effects
> >new connections that use "sql authentication". Below is
> a description
> >of my test scenario.
> >
> >a. Setup 3 machines.
> > i. one domain controller machine. windows 2000
> based. I have tried
> >both a regular domain setup and a domain setup in
> compatibility mode.
> > ii. one sql server machine that is a member of
> the domain. windows
> >2000.
> > iii. one client machine running an ado test
> program that communicates
> >with the sql server machine. I have tried both 2000 and
> xp.
> >
> >b. Start your test ado program and create a connection.
> >c. Fire a query.
> >d. It should work.
> >e. Unplug the network cord on the domain controller.
> >f. Create a new connection and fire a new connection
> about a minute or
> >so after.
> >g. It should work. Apparently the client caches account
> information
> >from the domain controller for a certain amount of
> time. The time
> >seems to be shorter by default in xp than 2000.
> >h. Wait 20 minutes or more.
> >i. Create another new connection. You will notice a
> timeout error.
> >If your using windows authentication the timeout will
> happen 100% of
> >the time. That is to be expected. If your using sql
> authentication
> >the timeout seems to happen about 50% of the time. I
> can't explain it
> >other than some strange Microsoft bug. I speculate that
> it may be
> >some bug with the way ado caches connections. Perhaps a
> previously
> >setup windows authentication connection gets reused by a
> request for a
> >sql authentication connection. However I haven't been
> able to prove
> >it.
> >
> >Any insight you can offer would be appreciated. I also
> have a test
> >program you can use to reproduce this behavior if you
> are interested.
> >
> >Thanks,
> >Frank
> >.
> >|||Thanks for the help. The mystery has been solved. I ended up opening
a ms support incident and they helped me figure it out. For those
that guessed dns server that was the right idea. However what made it
difficult to pin down is the following.
A. I tried as one person suggested connecting by ip address. I
thought this would eliminate dns issues from the equation. However as
the ms support guy informed me ado does a gethostbyaddr early in the
connection if you pass in an ip address. Therefore if the computer
can't do that your out of business.
B. I also thought that dns was not a factor because I could ping by
name after a connection failure and it would work. However the reason
it worked is because all the machines were on the same subnet and the
broadcast was what made it work. Once the dns server drops out the
first ping response was very slow. if you would do a ping immediately
after the response the 2nd ping would be fast. That behavior was what
was causing the randomness of the connection working. The first time
the connection would time out. However the attempt woke things up and
the second attempt would succeed.
Now that I know this I can route the important machines to a more
isolated and protected dns server or manually edit the hosts files to
eliminate the domain controller / dns controller that I can't control
from the equation.
frank@.policecentral.com (Frank) wrote in message news:<c148e27c.0401131411.32f26332@.posting.google.com>...
> I considered that possibility. I did bring up the "Client Network
> Utility" on the client and made tcp/ip the only choice. The behavior
> did not change. However I am referencing the machine by name and not
> ip. I will try the ip technique and report back.
> "Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> > Which network library are you using to establish the
> > connection? Named pipes will require an underlying
> > NetBIOS connection to the SQL Server, which may result in
> > the "cache-ing" behavior you are seeing. NetBIOS
> > connections will require NT user account authentication
> > and the use of a domain controller.
> >
> > If you refer to the SQL Server by IP Address in your
> > connectino string, you can force the client to connect
> > using TCP/IP which does not require an underlying Named
> > Pipe connection.
> >
> > I don't know if this will help, but it is one possible
> > explanation.
> >
> > Matthew Bando
> > BandoM@.CSCTGI(remove this).com
> >
> >
> >
> > >--Original Message--
> > >I have observed that a temporary loss of a domain
> controller can cause
> > >problems creating new ado connections between a client
> machine running
> > >ado and a separate sql server machine that are members
> of the domain.
> > >I understand why this happens when creating connections
> with windows
> > >authentication. What is a mystery is that it also
> sometimes effects
> > >new connections that use "sql authentication". Below is
> a description
> > >of my test scenario.
> > >
> > >a. Setup 3 machines.
> > > i. one domain controller machine. windows 2000
> based. I have tried
> > >both a regular domain setup and a domain setup in
> compatibility mode.
> > > ii. one sql server machine that is a member of
> the domain. windows
> > >2000.
> > > iii. one client machine running an ado test
> program that communicates
> > >with the sql server machine. I have tried both 2000 and
> xp.
> > >
> > >b. Start your test ado program and create a connection.
> > >c. Fire a query.
> > >d. It should work.
> > >e. Unplug the network cord on the domain controller.
> > >f. Create a new connection and fire a new connection
> about a minute or
> > >so after.
> > >g. It should work. Apparently the client caches account
> information
> > >from the domain controller for a certain amount of
> time. The time
> > >seems to be shorter by default in xp than 2000.
> > >h. Wait 20 minutes or more.
> > >i. Create another new connection. You will notice a
> timeout error.
> > >If your using windows authentication the timeout will
> happen 100% of
> > >the time. That is to be expected. If your using sql
> authentication
> > >the timeout seems to happen about 50% of the time. I
> can't explain it
> > >other than some strange Microsoft bug. I speculate that
> it may be
> > >some bug with the way ado caches connections. Perhaps a
> previously
> > >setup windows authentication connection gets reused by a
> request for a
> > >sql authentication connection. However I haven't been
> able to prove
> > >it.
> > >
> > >Any insight you can offer would be appreciated. I also
> have a test
> > >program you can use to reproduce this behavior if you
> are interested.
> > >
> > >Thanks,
> > >Frank
> > >.
> > >

ado "sql authentication" connections affected by loss of domain controller

quote:

> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. Windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank

quote:
darkred">
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon

I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.

Sunday, March 25, 2012

Adverse effects of too may "sleeping" connections

Thursday, March 8, 2012

aduit connections to SQL Server

Saturday, February 25, 2012

adodb connections to a linked server

Thursday, February 16, 2012

ADO Connections - close them or keep them open?

ADO Connections - close them or keep them open?

ADO Connections - close them or keep them open?

ADO Connections

Monday, February 13, 2012

ado "sql authentication" connections affected by loss of domain controller

ado "sql authentication" connections affected by loss of domain controller

ado "sql authentication" connections affected by loss of domain controller

SQL Adding a User account

Blog Archive

About Me