Running SQL Server as a DOMAIN user account.It is a single, two-edged sword. If SQL Server authenticates to the domain, it can have access to the resources of the domain. That is both a blessing and a curse.
It means that (given the proper permissions), the SQL Server can "see" other resources such as disk, printers, etc. The server can then send mail and other forms of messages (that rely on domain authentication).
In general, I usually have one "intereface" server that uses a domain account, but has no end user connections. It does all of the "cross server" work for the whole farm. The other servers use Local System unless some particular reason forces another choice.
-PatP|||Search for MS Best Practices on SQL Server and SQL Agent service accounts.|||with a domain user account the sqlserver and sql server agent accounts can access the local machine and can be audited through the os.
the mssqlserver service can communicate more efficiently with other servers that are domain members.
you can use sqlmail directly(no workarounds) because you will have an exchange mailbox created for your mssqlserver user account.
you can make the accounts [domain users] but give them admin rights on the local sql server machine to control access and use.
you can avoid having to create a cmdexec proxy account for running activex scripts ..
the accounts allow you the ability to perform active directory delegeation and impersonation.
the domain accounts provide for mutal authentication services through kerberos in active directory.sql
Showing posts with label domain. Show all posts
Showing posts with label domain. Show all posts
Tuesday, March 20, 2012
Monday, February 13, 2012
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.
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.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
> > >.
> > >
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
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...
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>...
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.
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...
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
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>...
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, February 12, 2012
Administrators
I would like that local administrators or domain administrators dont have
administrative rights on some databases running with MSDE.
I've learned about to enable the user "sa" and to give a personal password
to this user, but apparently administrators continue having full access to
all databases.
Thanks.
Regards.
Jose Nuez
Montevideo - Uruguay
hi Jose,
"Jose Nuez" <josenunez70@.hotmail.com> ha scritto nel messaggio
news:OOeUxFovEHA.164@.TK2MSFTNGP10.phx.gbl
> I would like that local administrators or domain administrators dont
> have administrative rights on some databases running with MSDE.
> I've learned about to enable the user "sa" and to give a personal
> password to this user, but apparently administrators continue having
> full access to all databases.
> Thanks.
> Regards.
> Jose Nuez
> Montevideo - Uruguay
Hari already answered you in june.. http://tinyurl.com/4638l and gave you
additional pointers to troubles you could experience..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
administrative rights on some databases running with MSDE.
I've learned about to enable the user "sa" and to give a personal password
to this user, but apparently administrators continue having full access to
all databases.
Thanks.
Regards.
Jose Nuez
Montevideo - Uruguay
hi Jose,
"Jose Nuez" <josenunez70@.hotmail.com> ha scritto nel messaggio
news:OOeUxFovEHA.164@.TK2MSFTNGP10.phx.gbl
> I would like that local administrators or domain administrators dont
> have administrative rights on some databases running with MSDE.
> I've learned about to enable the user "sa" and to give a personal
> password to this user, but apparently administrators continue having
> full access to all databases.
> Thanks.
> Regards.
> Jose Nuez
> Montevideo - Uruguay
Hari already answered you in june.. http://tinyurl.com/4638l and gave you
additional pointers to troubles you could experience..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
administrator password and sql startup
A reality check please...
After changing the domain administrator password, we are no longer
able to connect to databases on a MS SQL Server 2000 server when
attempting to use ODBC drivers from W2K workstations.
The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
\Administrator under the "Logon under This Account" in the 'Logon' tab
in services.msc. The server name inside the SQL server are set to
mixed mode.
Am I correct in thinking that changing the password field shown in the
"Login under This Account: DomainName\Administrator to match the new
domain administrator password will bring Joy?
Thanks!
BenoitSorry dude, I just saw this post. Yes, if you change a domain acct password
,
you have to change it in the services config box as well.
However, let me caution you... running SQL under an admin acct is bad
enough, but running it under the domain admin acct is just irresponsible.
Seriously, create a separate acct for SQL and give it local admin rights if
you have to, but DON"T use the domain admin acct.
"bntgoogle@.yahoo.com" wrote:
> A reality check please...
> After changing the domain administrator password, we are no longer
> able to connect to databases on a MS SQL Server 2000 server when
> attempting to use ODBC drivers from W2K workstations.
> The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
> \Administrator under the "Logon under This Account" in the 'Logon' tab
> in services.msc. The server name inside the SQL server are set to
> mixed mode.
> Am I correct in thinking that changing the password field shown in the
> "Login under This Account: DomainName\Administrator to match the new
> domain administrator password will bring Joy?
> Thanks!
> Benoit
>
After changing the domain administrator password, we are no longer
able to connect to databases on a MS SQL Server 2000 server when
attempting to use ODBC drivers from W2K workstations.
The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
\Administrator under the "Logon under This Account" in the 'Logon' tab
in services.msc. The server name inside the SQL server are set to
mixed mode.
Am I correct in thinking that changing the password field shown in the
"Login under This Account: DomainName\Administrator to match the new
domain administrator password will bring Joy?
Thanks!
BenoitSorry dude, I just saw this post. Yes, if you change a domain acct password
,
you have to change it in the services config box as well.
However, let me caution you... running SQL under an admin acct is bad
enough, but running it under the domain admin acct is just irresponsible.
Seriously, create a separate acct for SQL and give it local admin rights if
you have to, but DON"T use the domain admin acct.
"bntgoogle@.yahoo.com" wrote:
> A reality check please...
> After changing the domain administrator password, we are no longer
> able to connect to databases on a MS SQL Server 2000 server when
> attempting to use ODBC drivers from W2K workstations.
> The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
> \Administrator under the "Logon under This Account" in the 'Logon' tab
> in services.msc. The server name inside the SQL server are set to
> mixed mode.
> Am I correct in thinking that changing the password field shown in the
> "Login under This Account: DomainName\Administrator to match the new
> domain administrator password will bring Joy?
> Thanks!
> Benoit
>
administrator password and sql startup
A reality check please...
After changing the domain administrator password, we are no longer
able to connect to databases on a MS SQL Server 2000 server when
attempting to use ODBC drivers from W2K workstations.
The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
\Administrator under the "Logon under This Account" in the 'Logon' tab
in services.msc. The server name inside the SQL server are set to
mixed mode.
Am I correct in thinking that changing the password field shown in the
"Login under This Account: DomainName\Administrator to match the new
domain administrator password will bring Joy?
Thanks!
Benoit
Sorry dude, I just saw this post. Yes, if you change a domain acct password,
you have to change it in the services config box as well.
However, let me caution you... running SQL under an admin acct is bad
enough, but running it under the domain admin acct is just irresponsible.
Seriously, create a separate acct for SQL and give it local admin rights if
you have to, but DON"T use the domain admin acct.
"bntgoogle@.yahoo.com" wrote:
> A reality check please...
> After changing the domain administrator password, we are no longer
> able to connect to databases on a MS SQL Server 2000 server when
> attempting to use ODBC drivers from W2K workstations.
> The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
> \Administrator under the "Logon under This Account" in the 'Logon' tab
> in services.msc. The server name inside the SQL server are set to
> mixed mode.
> Am I correct in thinking that changing the password field shown in the
> "Login under This Account: DomainName\Administrator to match the new
> domain administrator password will bring Joy?
> Thanks!
> Benoit
>
After changing the domain administrator password, we are no longer
able to connect to databases on a MS SQL Server 2000 server when
attempting to use ODBC drivers from W2K workstations.
The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
\Administrator under the "Logon under This Account" in the 'Logon' tab
in services.msc. The server name inside the SQL server are set to
mixed mode.
Am I correct in thinking that changing the password field shown in the
"Login under This Account: DomainName\Administrator to match the new
domain administrator password will bring Joy?
Thanks!
Benoit
Sorry dude, I just saw this post. Yes, if you change a domain acct password,
you have to change it in the services config box as well.
However, let me caution you... running SQL under an admin acct is bad
enough, but running it under the domain admin acct is just irresponsible.
Seriously, create a separate acct for SQL and give it local admin rights if
you have to, but DON"T use the domain admin acct.
"bntgoogle@.yahoo.com" wrote:
> A reality check please...
> After changing the domain administrator password, we are no longer
> able to connect to databases on a MS SQL Server 2000 server when
> attempting to use ODBC drivers from W2K workstations.
> The MSSQLSERVER and SQLSERVERAGENT services both show DomainName
> \Administrator under the "Logon under This Account" in the 'Logon' tab
> in services.msc. The server name inside the SQL server are set to
> mixed mode.
> Am I correct in thinking that changing the password field shown in the
> "Login under This Account: DomainName\Administrator to match the new
> domain administrator password will bring Joy?
> Thanks!
> Benoit
>
Thursday, February 9, 2012
Administering many SQL Servers
We have 25+ SQL Servers our DBA group administers. Each is installed under a
domain account, call it Domain1\SQLAdmin. When we change the password to
Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server Agent
password properties on each box to be the new password. Is there any way to
automate this process?Hi
No. We have a similar problem with 1400 servers and are going to have each
server have it's own account and password.
This can reduce the likelihood of on compromised server being used to
compromise other, have developers connect from development to production
though linked servers or xp_cmdshell, and a denial of service (account
lockout) against the service account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> We have 25+ SQL Servers our DBA group administers. Each is installed under
> a
> domain account, call it Domain1\SQLAdmin. When we change the password to
> Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> Agent
> password properties on each box to be the new password. Is there any way
> to
> automate this process?|||I was afraid that was the answer. Fortunately we don't have 1400 nodes to
worry about. Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. We have a similar problem with 1400 servers and are going to have each
> server have it's own account and password.
> This can reduce the likelihood of on compromised server being used to
> compromise other, have developers connect from development to production
> though linked servers or xp_cmdshell, and a denial of service (account
> lockout) against the service account.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
> news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> > We have 25+ SQL Servers our DBA group administers. Each is installed under
> > a
> > domain account, call it Domain1\SQLAdmin. When we change the password to
> > Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> > Agent
> > password properties on each box to be the new password. Is there any way
> > to
> > automate this process?
>
>
domain account, call it Domain1\SQLAdmin. When we change the password to
Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server Agent
password properties on each box to be the new password. Is there any way to
automate this process?Hi
No. We have a similar problem with 1400 servers and are going to have each
server have it's own account and password.
This can reduce the likelihood of on compromised server being used to
compromise other, have developers connect from development to production
though linked servers or xp_cmdshell, and a denial of service (account
lockout) against the service account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> We have 25+ SQL Servers our DBA group administers. Each is installed under
> a
> domain account, call it Domain1\SQLAdmin. When we change the password to
> Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> Agent
> password properties on each box to be the new password. Is there any way
> to
> automate this process?|||I was afraid that was the answer. Fortunately we don't have 1400 nodes to
worry about. Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. We have a similar problem with 1400 servers and are going to have each
> server have it's own account and password.
> This can reduce the likelihood of on compromised server being used to
> compromise other, have developers connect from development to production
> though linked servers or xp_cmdshell, and a denial of service (account
> lockout) against the service account.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
> news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> > We have 25+ SQL Servers our DBA group administers. Each is installed under
> > a
> > domain account, call it Domain1\SQLAdmin. When we change the password to
> > Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> > Agent
> > password properties on each box to be the new password. Is there any way
> > to
> > automate this process?
>
>
Admin access to SQL without server/domain admin access
We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
Any clarification would be greatly appreciated.
Thanks!
Part of the problem may be from using a more generic phrase like "admin level access to SQL". What that means to you, I'm not sure. That could be interpreted as administering the server from the windows level or it could mean being a member of the sysadmins server role in SQL Server.
But you don't need to be a member of a windows windows administrator group (local administrator on the server or domain administrator in the domain) to be a member of the sysamin server role in SQL Server. A member of the sysadmin role in SQL Server can add other logins to the sysadmin role - and anything thing else they want to do as members of the sysadmins role bypass security checks and can do anything in SQL Server. This is what it sounds like you are asking about.
I have seen some similar confusion coming from this type of scenario:
You have your SQL Server running under a service account and that account is a member of the local administrators group on the server on which SQL Server is running
You are a member of the sysadmin server role in SQL Server
Some of the command you execute as a member of the sysadmin role will execute under the security context of the service account. Being that the service account is a member of the local administrators group, you are executing some of the commands as if you are a member of the local administrators group on that server.
But that scenario doesn't mean you are a member of the local administrators group or that you need to be a member of the local administrators group.
-Sue
Subscribe to:
Posts (Atom)