I have a SQL 2000 (SP3) running on a Windows NT 4.0 (SP6) box used in our test environment. The SQL Server was configured to run under the local system account before I got here. In an effort to standardize things, I tried changing the SQL Service account to run under a designated domain user account purpose built for the job. We use this particular account for all of our new-build servers (which are W2K). This domain account is configured to be a "Power User" on the NT 4.0 Server in question.
Soon after changing things over to run under the new account, all the developers complained that they could no longer connect to the server. I could through QA and EM, but none of the developers could.
The developers are using WebLogic and JDBC drivers for the most part. I wasn't aware that the SQL Server service account affected client connectivity. Was I wrong or is there something else at work here?
Thanks,
hmscottDamn...I'm dealing with something similar right now...
The SSSA run things on behalf of the server...
My guess is that they all connect using sa blank (or whatever, connection pooling id and are still connectiong using sql server auth) and now that it's trusted, their connections are wrong...
How do they connect?
Like when you register a server in EM?
I don't think the service account has anything to do with it...
MOO|||Hi Brett,
They're connecting via various user accounts that are application-specific. Unfortunately, I am only beginning to make progress on convincing management to swing to all trusted connections. The developers insist that WebLogic doesn't support the concept of running under a service account.
Anyway, one guy was using sa, others were using different application accounts. I was able to connect successfully using a trusted connection (but then, I'm a Domain Admin, too, so there's no telling for sure.
It's far from an ideal setup; I'm just trying to correct things bit by bit.
Regards,
Hugh|||Hi Brett,
The developers insist that WebLogic doesn't support the concept of running under a service account.
That may be true...we use websphere and we set a connection pooling account that authenticates through sql server...but it's 1 id and many connections
Anyway, one guy was using sa
Hugh
There's always one...
Still no one is using the SSSA to connect...right?|||No, no one is using that account. I'm sure because the pasword is quite complex and other than being documented in a restricted folder for the DBAs, it's not known.
Thanks for your time...
Regards,
hmscott
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Thursday, March 29, 2012
Changing the SQL Server Agent ID
Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peter
it's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter
|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peter
it's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter
|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>
Changing the SQL Server Agent ID
Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...eagreen">
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>
>
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...eagreen">
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>
>
Changing the SQL Server Agent ID
Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
> > Hello,
> >
> > We have are using SQL 2000 on a 2003 server. We have one
> > instance of 2000 up to service pack 3a.
> >
> > We need to change the domain user id of the SQL Server
> > Agent. We changed it through the Services section of
> > Windows 2003. Then starting up it reports 'Access is
> > deniged'
> >
> > The domain userid we are change it to is already an
> > administrator on the server, and is used on a different
> > server, and I am logged on as a local administrator.
> >
> > Can anyone point the way ?
> >
> > Thanks
> > Peter
>sql
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
> > Hello,
> >
> > We have are using SQL 2000 on a 2003 server. We have one
> > instance of 2000 up to service pack 3a.
> >
> > We need to change the domain user id of the SQL Server
> > Agent. We changed it through the Services section of
> > Windows 2003. Then starting up it reports 'Access is
> > deniged'
> >
> > The domain userid we are change it to is already an
> > administrator on the server, and is used on a different
> > server, and I am logged on as a local administrator.
> >
> > Can anyone point the way ?
> >
> > Thanks
> > Peter
>sql
Tuesday, March 27, 2012
Changing the MSSQLServer service account causes SQL Agent could not start
Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.
Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John
|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.
Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John
|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
Changing the MSSQLServer service account causes SQL Agent could not start
Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
running either a domain account or local system account, i.e.
MSSQLServer service is under a domain account, SQL Agent is under
local system.
For a reason, we need to switch the domain account to the other domain
account, the MSSQLServer service is started up with no problem but the
SQL Agent is not able to start up neither in local system or new
domain account, the error message is: "The SQL Server Agent
(MSSQLSERVER) service on Local Computer started and then stop
automatically if they have no work to do, for example, the Performance
Logs and Alerts service. "
Switch back to original domain account is fine, let the MSSQLServer
running on local system account is fine too for both MSSQLServer and
SQL Agent.
P.S. Both old/new domain accounts are in the server's local admin and
domain administrator groups and as well as SQL sa.
What is needed to use a domain account as the SQL service account?
Thanks,
Yvette.Hi Yvette
"yvette.ye@.gmail.com" wrote:
> Hi...a SQL2005 enterprise under W2K3 server. All the SQL services are
> running either a domain account or local system account, i.e.
> MSSQLServer service is under a domain account, SQL Agent is under
> local system.
> For a reason, we need to switch the domain account to the other domain
> account, the MSSQLServer service is started up with no problem but the
> SQL Agent is not able to start up neither in local system or new
> domain account, the error message is: "The SQL Server Agent
> (MSSQLSERVER) service on Local Computer started and then stop
> automatically if they have no work to do, for example, the Performance
> Logs and Alerts service. "
> Switch back to original domain account is fine, let the MSSQLServer
> running on local system account is fine too for both MSSQLServer and
> SQL Agent.
> P.S. Both old/new domain accounts are in the server's local admin and
> domain administrator groups and as well as SQL sa.
> What is needed to use a domain account as the SQL service account?
> Thanks,
> Yvette.
>
For the account types that can be used see
http://support.microsoft.com/kb/907557
and http://support.microsoft.com/kb/283811 describes the requirements for
the accounts if you don't use EM or SCM to change the account.
John|||"John Bell" wrote:
> Hi Yvette
> For the account types that can be used see
> http://support.microsoft.com/kb/907557
> and http://support.microsoft.com/kb/283811 describes the requirements for
> the accounts if you don't use EM or SCM to change the account.
> John
Also try http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
Tuesday, March 20, 2012
Changing startup-account from admin- to system account
Hi, after changing the service-startup-account from an admin-account to the system-account the service starts fine but users cannot longer connect. They use a SQL-server login to connect. Authentication is set to 'sql-server and windows' The change was do
ne from enterprise manager on the server running the service, the server was rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni Santa
What error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa
|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
>
>
ne from enterprise manager on the server running the service, the server was rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni Santa
What error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa
|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
>
>
Changing startup-account from admin- to system account
Hi, after changing the service-startup-account from an admin-account to the system-account the service starts fine but users cannot longer connect. They use a SQL-server login to connect. Authentication is set to 'sql-server and windows' The change was done from enterprise manager on the server running the service, the server was rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Thanks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> > Hi, after changing the service-startup-account from an admin-account to
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
> > Any help appreciated.
> > Toni Santa
>
>
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been changed by the customer. For know it has been reset to an admin-account and so he is able to work. I will check this when I pass in his office, could be next week.
My application connects via the servername? I found a thread in the SQL Server Security group where someone states that after changing some security issue of SQL Server he could connect only via IP-address. I will try this, too and take you informed. Thanks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure are
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> > Hi, after changing the service-startup-account from an admin-account to
> the system-account the service starts fine but users cannot longer connect.
> They use a SQL-server login to connect. Authentication is set to 'sql-server
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Small
> Business Server 2000.
> > Any help appreciated.
> > Toni Santa
>
>
Changing startup-account from admin- to system account
Hi, after changing the service-startup-account from an admin-account to the
system-account the service starts fine but users cannot longer connect. They
use a SQL-server login to connect. Authentication is set to 'sql-server and
windows' The change was do
ne from enterprise manager on the server running the service, the server was
rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been ch
anged by the customer. For know it has been reset to an admin-account and so
he is able to work. I will check this when I pass in his office, could be n
ext week.
My application connects via the servername? I found a thread in the SQL Serv
er Security group where someone states that after changing some security iss
ue of SQL Server he could connect only via IP-address. I will try this, too
and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure a
re
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect
.
> They use a SQL-server login to connect. Authentication is set to 'sql-serv
er
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Smal
l
> Business Server 2000.
>
>
system-account the service starts fine but users cannot longer connect. They
use a SQL-server login to connect. Authentication is set to 'sql-server and
windows' The change was do
ne from enterprise manager on the server running the service, the server was
rebooted. The operating system is Small Business Server 2000.
Any help appreciated.
Toni SantaWhat error do the users get? What is your AuditLevel setting? You might
want to set it to "3",. so all attempts regardless of success or failure are
logged. Can you connect via say Query Analyzer when you are logged on
locally? Can you see the SQL Server box on the network?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> Hi, after changing the service-startup-account from an admin-account to
the system-account the service starts fine but users cannot longer connect.
They use a SQL-server login to connect. Authentication is set to 'sql-server
and windows' The change was done from enterprise manager on the server
running the service, the server was rebooted. The operating system is Small
Business Server 2000.
> Any help appreciated.
> Toni Santa|||Hi Gregory, for now I don't know this. The service-start-account has been ch
anged by the customer. For know it has been reset to an admin-account and so
he is able to work. I will check this when I pass in his office, could be n
ext week.
My application connects via the servername? I found a thread in the SQL Serv
er Security group where someone states that after changing some security iss
ue of SQL Server he could connect only via IP-address. I will try this, too
and take you informed. Than
ks - Toni Santa
"Gregory A. Larsen" wrote:
> What error do the users get? What is your AuditLevel setting? You might
> want to set it to "3",. so all attempts regardless of success or failure a
re
> logged. Can you connect via say Query Analyzer when you are logged on
> locally? Can you see the SQL Server box on the network?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Toni Santa" <Toni Santa@.discussions.microsoft.com> wrote in message
> news:B07674C0-EC3A-40B5-81B5-5F313147DF49@.microsoft.com...
> the system-account the service starts fine but users cannot longer connect
.
> They use a SQL-server login to connect. Authentication is set to 'sql-serv
er
> and windows' The change was done from enterprise manager on the server
> running the service, the server was rebooted. The operating system is Smal
l
> Business Server 2000.
>
>
Changing SQL Service account passowrd on a cluster configuration
Hi,
I have SQL 2005 server (with Named Instance) running on a two node Cluster
Configuration (Active/Passive).
SQL Instance, SQL Server Agent, SQL Server Browser services are running
under a service account (domain account). Similarly Cluster service is also
running under a cluster service account (domain account).
I am looking for accurate steps to update password of SQL Service account
and Cluster service account that will cause minimum disruption of these
services. If there’s a link to documentation on how to update password, that
will be most useful.
For the cluster service account password:
http://support.microsoft.com/kb/305813/en-us
For the SQL 2005 Instance, use the SQL Configuration Manager to change the
password. It handles the "cluster magic".
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
> Hi,
> I have SQL 2005 server (with Named Instance) running on a two node Cluster
> Configuration (Active/Passive).
> SQL Instance, SQL Server Agent, SQL Server Browser services are running
> under a service account (domain account). Similarly Cluster service is
> also
> running under a cluster service account (domain account).
> I am looking for accurate steps to update password of SQL Service account
> and Cluster service account that will cause minimum disruption of these
> services. If there’s a link to documentation on how to update password,
> that
> will be most useful.
>
|||It could be just me, but I had problem using Configuration Manager to change
the SQL service account password from time to time. I don't remember the
exact error message now, but I remember not having success in getting the
change replicated among the nodes.
I've had more success with just using the services.msc mgmt console to
change the SQL service account password on each node. This is just a hassle
since on each node there are a few services to change. I know Configuration
Manager is not cluster aware, but had hoped that at least any change made
through it to the SQL registry entries would be automatically replicated by
the cluster service.
Linchi
"Geoff N. Hiten" wrote:
> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Thanks Geoff. But I would really like to know what are the steps to perform.
Do I need to change password on both the nodes or only on the active node ?
Once the password for the account is updated in AD, is it immediately
reflected on the SQL servers ?
"Geoff N. Hiten" wrote:
> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Linchi, Thanks for your feedback.
So what steps do you follow to update password for all these services using
services.msc ?
"Linchi Shea" wrote:
[vbcol=seagreen]
> It could be just me, but I had problem using Configuration Manager to change
> the SQL service account password from time to time. I don't remember the
> exact error message now, but I remember not having success in getting the
> change replicated among the nodes.
> I've had more success with just using the services.msc mgmt console to
> change the SQL service account password on each node. This is just a hassle
> since on each node there are a few services to change. I know Configuration
> Manager is not cluster aware, but had hoped that at least any change made
> through it to the SQL registry entries would be automatically replicated by
> the cluster service.
> Linchi
> "Geoff N. Hiten" wrote:
|||Here are the steps I went through recently to update the password for the SQL
service account (the previous password expired. It shouldn't be set to
expire, but that's a different story):
1. Remote desktop to each node
2. Start -> Run, type services.msc
3. In the service list, locate all the SQL Server related services that use
the password.
4. Double click on each such service
5. Click on the "Log On' tab.
6. Type in the new password in the Password and Confirm Password textboxes.
7. Click on Apply and OK.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi, Thanks for your feedback.
> So what steps do you follow to update password for all these services using
> services.msc ?
> "Linchi Shea" wrote:
|||LInchi's advice on using the services applet on each node is a bit more
complex, but it is a certaintly.
As for when changes "take", AD may need up to fifteen minutes to replicate
the password change through the system when you have multiple controllers.
If an account is logged in to a resource, such as a service account already
running, you can leave it running or a while before changing it. The system
will not force it out immediately but the application may no longer be able
to access network resources after some time.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:7F18E2CD-E089-4642-898E-FB303324181E@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff. But I would really like to know what are the steps to
> perform.
> Do I need to change password on both the nodes or only on the active node
> ?
> Once the password for the account is updated in AD, is it immediately
> reflected on the SQL servers ?
> "Geoff N. Hiten" wrote:
|||Linchi,
Here's what worked for me. Quite similar to the steps you have outlined:
1.Changed SQL Service account password in AD. Waited for about half an hour
so that password change gets replicated to all the domain controllers.
2.Updated the password on the active node of the SQL Cluster using SQL
Configuration manager for all the services.
3.Restarted the services using SCM. Everything looked fine till now.
4.On the passive node, using Services MMC, manually updated password for
the SQL services.
5.Fail over the cluster from node 1 to node 2, everything worked fine with
no errors.
I will be blogging these steps, but hope that other people who are in the
same situation will find this discussion useful.
Thanks Geoff and Linchi for your quick responses.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Here are the steps I went through recently to update the password for the SQL
> service account (the previous password expired. It shouldn't be set to
> expire, but that's a different story):
> 1. Remote desktop to each node
> 2. Start -> Run, type services.msc
> 3. In the service list, locate all the SQL Server related services that use
> the password.
> 4. Double click on each such service
> 5. Click on the "Log On' tab.
> 6. Type in the new password in the Password and Confirm Password textboxes.
> 7. Click on Apply and OK.
> Linchi
> "Mehul" wrote:
|||I'm glad that you included step 5 to failover the SQL group among the nodes.
That's an abosolutely critical step to close loop the whole task.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi,
> Here's what worked for me. Quite similar to the steps you have outlined:
> 1.Changed SQL Service account password in AD. Waited for about half an hour
> so that password change gets replicated to all the domain controllers.
> 2.Updated the password on the active node of the SQL Cluster using SQL
> Configuration manager for all the services.
> 3.Restarted the services using SCM. Everything looked fine till now.
> 4.On the passive node, using Services MMC, manually updated password for
> the SQL services.
> 5.Fail over the cluster from node 1 to node 2, everything worked fine with
> no errors.
> I will be blogging these steps, but hope that other people who are in the
> same situation will find this discussion useful.
> Thanks Geoff and Linchi for your quick responses.
>
> "Linchi Shea" wrote:
I have SQL 2005 server (with Named Instance) running on a two node Cluster
Configuration (Active/Passive).
SQL Instance, SQL Server Agent, SQL Server Browser services are running
under a service account (domain account). Similarly Cluster service is also
running under a cluster service account (domain account).
I am looking for accurate steps to update password of SQL Service account
and Cluster service account that will cause minimum disruption of these
services. If there’s a link to documentation on how to update password, that
will be most useful.
For the cluster service account password:
http://support.microsoft.com/kb/305813/en-us
For the SQL 2005 Instance, use the SQL Configuration Manager to change the
password. It handles the "cluster magic".
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
> Hi,
> I have SQL 2005 server (with Named Instance) running on a two node Cluster
> Configuration (Active/Passive).
> SQL Instance, SQL Server Agent, SQL Server Browser services are running
> under a service account (domain account). Similarly Cluster service is
> also
> running under a cluster service account (domain account).
> I am looking for accurate steps to update password of SQL Service account
> and Cluster service account that will cause minimum disruption of these
> services. If there’s a link to documentation on how to update password,
> that
> will be most useful.
>
|||It could be just me, but I had problem using Configuration Manager to change
the SQL service account password from time to time. I don't remember the
exact error message now, but I remember not having success in getting the
change replicated among the nodes.
I've had more success with just using the services.msc mgmt console to
change the SQL service account password on each node. This is just a hassle
since on each node there are a few services to change. I know Configuration
Manager is not cluster aware, but had hoped that at least any change made
through it to the SQL registry entries would be automatically replicated by
the cluster service.
Linchi
"Geoff N. Hiten" wrote:
> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Thanks Geoff. But I would really like to know what are the steps to perform.
Do I need to change password on both the nodes or only on the active node ?
Once the password for the account is updated in AD, is it immediately
reflected on the SQL servers ?
"Geoff N. Hiten" wrote:
> For the cluster service account password:
> http://support.microsoft.com/kb/305813/en-us
> For the SQL 2005 Instance, use the SQL Configuration Manager to change the
> password. It handles the "cluster magic".
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Mehul" <Mehul@.discussions.microsoft.com> wrote in message
> news:C4EE5D43-00E3-4322-92C2-538B8CDEC12D@.microsoft.com...
>
|||Linchi, Thanks for your feedback.
So what steps do you follow to update password for all these services using
services.msc ?
"Linchi Shea" wrote:
[vbcol=seagreen]
> It could be just me, but I had problem using Configuration Manager to change
> the SQL service account password from time to time. I don't remember the
> exact error message now, but I remember not having success in getting the
> change replicated among the nodes.
> I've had more success with just using the services.msc mgmt console to
> change the SQL service account password on each node. This is just a hassle
> since on each node there are a few services to change. I know Configuration
> Manager is not cluster aware, but had hoped that at least any change made
> through it to the SQL registry entries would be automatically replicated by
> the cluster service.
> Linchi
> "Geoff N. Hiten" wrote:
|||Here are the steps I went through recently to update the password for the SQL
service account (the previous password expired. It shouldn't be set to
expire, but that's a different story):
1. Remote desktop to each node
2. Start -> Run, type services.msc
3. In the service list, locate all the SQL Server related services that use
the password.
4. Double click on each such service
5. Click on the "Log On' tab.
6. Type in the new password in the Password and Confirm Password textboxes.
7. Click on Apply and OK.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi, Thanks for your feedback.
> So what steps do you follow to update password for all these services using
> services.msc ?
> "Linchi Shea" wrote:
|||LInchi's advice on using the services applet on each node is a bit more
complex, but it is a certaintly.
As for when changes "take", AD may need up to fifteen minutes to replicate
the password change through the system when you have multiple controllers.
If an account is logged in to a resource, such as a service account already
running, you can leave it running or a while before changing it. The system
will not force it out immediately but the application may no longer be able
to access network resources after some time.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Mehul" <Mehul@.discussions.microsoft.com> wrote in message
news:7F18E2CD-E089-4642-898E-FB303324181E@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff. But I would really like to know what are the steps to
> perform.
> Do I need to change password on both the nodes or only on the active node
> ?
> Once the password for the account is updated in AD, is it immediately
> reflected on the SQL servers ?
> "Geoff N. Hiten" wrote:
|||Linchi,
Here's what worked for me. Quite similar to the steps you have outlined:
1.Changed SQL Service account password in AD. Waited for about half an hour
so that password change gets replicated to all the domain controllers.
2.Updated the password on the active node of the SQL Cluster using SQL
Configuration manager for all the services.
3.Restarted the services using SCM. Everything looked fine till now.
4.On the passive node, using Services MMC, manually updated password for
the SQL services.
5.Fail over the cluster from node 1 to node 2, everything worked fine with
no errors.
I will be blogging these steps, but hope that other people who are in the
same situation will find this discussion useful.
Thanks Geoff and Linchi for your quick responses.
"Linchi Shea" wrote:
[vbcol=seagreen]
> Here are the steps I went through recently to update the password for the SQL
> service account (the previous password expired. It shouldn't be set to
> expire, but that's a different story):
> 1. Remote desktop to each node
> 2. Start -> Run, type services.msc
> 3. In the service list, locate all the SQL Server related services that use
> the password.
> 4. Double click on each such service
> 5. Click on the "Log On' tab.
> 6. Type in the new password in the Password and Confirm Password textboxes.
> 7. Click on Apply and OK.
> Linchi
> "Mehul" wrote:
|||I'm glad that you included step 5 to failover the SQL group among the nodes.
That's an abosolutely critical step to close loop the whole task.
Linchi
"Mehul" wrote:
[vbcol=seagreen]
> Linchi,
> Here's what worked for me. Quite similar to the steps you have outlined:
> 1.Changed SQL Service account password in AD. Waited for about half an hour
> so that password change gets replicated to all the domain controllers.
> 2.Updated the password on the active node of the SQL Cluster using SQL
> Configuration manager for all the services.
> 3.Restarted the services using SCM. Everything looked fine till now.
> 4.On the passive node, using Services MMC, manually updated password for
> the SQL services.
> 5.Fail over the cluster from node 1 to node 2, everything worked fine with
> no errors.
> I will be blogging these steps, but hope that other people who are in the
> same situation will find this discussion useful.
> Thanks Geoff and Linchi for your quick responses.
>
> "Linchi Shea" wrote:
Changing SQL Service Account
Has anyone ever converted from running SQL Server under the Local System account to running under a Domain User account?
I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.
If you have any input or insights, I would be grateful.
Regards,
hmscottI have done this several times, and have had no issues (knock on wood). Make sure that the Domain account has sufficient permissions on the local machine, and you shold be ok.|||Is "Power User" sufficient, or do I have to grant local admin to the account?
Regards,
hmscott|||If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.|||Originally posted by Satya
If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.
8-O
That's wrong! Basic tenet of security is least privileges of course. Required permissions are outlined in this article:
http://support.microsoft.com/?id=283811
Quote from the article: "...running SQL Server under such high user rights is not recommended."|||No such threat at our end, so far so good.
It purely depend how you secure the network and connections.|||Due to the nature of what our SQL Servers do, we make most of the machines run as LocalSystem. We basically make each machine run with the lowest level of privledge that it needs to do its job.
We do have one machine that is our interface/automation server that does all kinds of things like copying data from one server to another, runs DTS packages that affect multiple machines, etc that has privleges similar to a Domain Admin (because it must touch nearly every machine in the Data Center). Only the Domain Admins and a few select IT staff can even see this box, much less touch it!
-PatP
I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.
If you have any input or insights, I would be grateful.
Regards,
hmscottI have done this several times, and have had no issues (knock on wood). Make sure that the Domain account has sufficient permissions on the local machine, and you shold be ok.|||Is "Power User" sufficient, or do I have to grant local admin to the account?
Regards,
hmscott|||If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.|||Originally posted by Satya
If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.
8-O
That's wrong! Basic tenet of security is least privileges of course. Required permissions are outlined in this article:
http://support.microsoft.com/?id=283811
Quote from the article: "...running SQL Server under such high user rights is not recommended."|||No such threat at our end, so far so good.
It purely depend how you secure the network and connections.|||Due to the nature of what our SQL Servers do, we make most of the machines run as LocalSystem. We basically make each machine run with the lowest level of privledge that it needs to do its job.
We do have one machine that is our interface/automation server that does all kinds of things like copying data from one server to another, runs DTS packages that affect multiple machines, etc that has privleges similar to a Domain Admin (because it must touch nearly every machine in the Data Center). Only the Domain Admins and a few select IT staff can even see this box, much less touch it!
-PatP
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers
,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
>sql
,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
>sql
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
Bruce
Bruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H
|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>
|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
Bruce
Bruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H
|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>
|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
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/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
changing sql server service account password
SQL Server 2000 running on W2K3 Advanced Server Cluster with 2 nodes.
We have a domain level account which the MSSQLSERVER and SQLSERVERAGENT
services use.
When I change the password for this account in active directory, I also
changed the password for the services in the Services properties on both
cluster nodes
The services Startup Type has to be Manual, or else the following error
occurs:
"17050: initerrlog: could not open error log file ... (the correct path
to the error log follows)"
The error log is on the shared drive of the cluster.
Also, when the servers boot up, the following error is in Event Viewer:
"The Data portion of event 19002 from MSSQLServer is invalid"
Microsoft (Q230393) says this is a bug and ignore it, but it didn't occur
until I changed the password.
Are these normal consequences of changing the account password?
Are there other steps I should take?
Thanks
Bill
Go back into Enterprise Manager and change the service accounts there. That
will fix any permissions and setup issues. Service startup type as Manual
is correct. That allows the cluster service to control the actual service
start/stop.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"bill" <belgie@.datamti.com> wrote in message
news:uOYfAolLFHA.904@.tk2msftngp13.phx.gbl...
> SQL Server 2000 running on W2K3 Advanced Server Cluster with 2 nodes.
> We have a domain level account which the MSSQLSERVER and SQLSERVERAGENT
> services use.
> When I change the password for this account in active directory, I also
> changed the password for the services in the Services properties on both
> cluster nodes
> The services Startup Type has to be Manual, or else the following error
> occurs:
> "17050: initerrlog: could not open error log file ... (the correct path
> to the error log follows)"
> The error log is on the shared drive of the cluster.
> Also, when the servers boot up, the following error is in Event Viewer:
> "The Data portion of event 19002 from MSSQLServer is invalid"
> Microsoft (Q230393) says this is a bug and ignore it, but it didn't occur
> until I changed the password.
> Are these normal consequences of changing the account password?
> Are there other steps I should take?
> Thanks
> Bill
>
>
We have a domain level account which the MSSQLSERVER and SQLSERVERAGENT
services use.
When I change the password for this account in active directory, I also
changed the password for the services in the Services properties on both
cluster nodes
The services Startup Type has to be Manual, or else the following error
occurs:
"17050: initerrlog: could not open error log file ... (the correct path
to the error log follows)"
The error log is on the shared drive of the cluster.
Also, when the servers boot up, the following error is in Event Viewer:
"The Data portion of event 19002 from MSSQLServer is invalid"
Microsoft (Q230393) says this is a bug and ignore it, but it didn't occur
until I changed the password.
Are these normal consequences of changing the account password?
Are there other steps I should take?
Thanks
Bill
Go back into Enterprise Manager and change the service accounts there. That
will fix any permissions and setup issues. Service startup type as Manual
is correct. That allows the cluster service to control the actual service
start/stop.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"bill" <belgie@.datamti.com> wrote in message
news:uOYfAolLFHA.904@.tk2msftngp13.phx.gbl...
> SQL Server 2000 running on W2K3 Advanced Server Cluster with 2 nodes.
> We have a domain level account which the MSSQLSERVER and SQLSERVERAGENT
> services use.
> When I change the password for this account in active directory, I also
> changed the password for the services in the Services properties on both
> cluster nodes
> The services Startup Type has to be Manual, or else the following error
> occurs:
> "17050: initerrlog: could not open error log file ... (the correct path
> to the error log follows)"
> The error log is on the shared drive of the cluster.
> Also, when the servers boot up, the following error is in Event Viewer:
> "The Data portion of event 19002 from MSSQLServer is invalid"
> Microsoft (Q230393) says this is a bug and ignore it, but it didn't occur
> until I changed the password.
> Are these normal consequences of changing the account password?
> Are there other steps I should take?
> Thanks
> Bill
>
>
changing SQL server service account
Guys,
I have got WINDOWS 2000 Advanced Server and MS SQL SERVER 7.0 running on my live server. Now when we are planning for replication, we have found that SQL server will require to run under a domain account. At the moment there are so many ASP pages running on our server accesses different databases created using SQL server 7.0. Most of them are DSN connections to the database. Now if i create a domain account and restart the server and MS SQL services with the domain account, how is it going to effect the current web pages running on it?
Any help will be greatly appreciated.
ThanksA connection to a database has no bearing on what account starts the sql server service. However the account used does affect how the server interacts with other sql servers, or other machine resources, you found that out when you tried to implement replication using LocalSystem account. You can search msdn for "sql server service account" to get a better understanding of the purpose of accounts for sql server.|||Thanks very much Greg,
your reply helped me to have better understanding.
I would find out more from MSDN as you have suggested.
Cheers.
I have got WINDOWS 2000 Advanced Server and MS SQL SERVER 7.0 running on my live server. Now when we are planning for replication, we have found that SQL server will require to run under a domain account. At the moment there are so many ASP pages running on our server accesses different databases created using SQL server 7.0. Most of them are DSN connections to the database. Now if i create a domain account and restart the server and MS SQL services with the domain account, how is it going to effect the current web pages running on it?
Any help will be greatly appreciated.
ThanksA connection to a database has no bearing on what account starts the sql server service. However the account used does affect how the server interacts with other sql servers, or other machine resources, you found that out when you tried to implement replication using LocalSystem account. You can search msdn for "sql server service account" to get a better understanding of the purpose of accounts for sql server.|||Thanks very much Greg,
your reply helped me to have better understanding.
I would find out more from MSDN as you have suggested.
Cheers.
Monday, March 19, 2012
Changing SQL and Agent Service account?
Are there any best practices recommending changing the SQL Server/Agent
domain service account password? I assume you do not want to leave these
the same forever after install, and I have a service pwd change utility
without restarting SQL server, but not seeing a lot of talk about changing
these account(s). Should they not change just like any other priveledged
user?"Brian" <bpollard@.idahodba.org> wrote in message
news:OCqXZ8KdEHA.3016@.tk2msftngp13.phx.gbl...
> Are there any best practices recommending changing the SQL Server/Agent
> domain service account password? I assume you do not want to leave these
> the same forever after install, and I have a service pwd change utility
> without restarting SQL server, but not seeing a lot of talk about changing
> these account(s). Should they not change just like any other priveledged
> user?
>
If you change these accounts it is probably easiest to do so through
Enterprise Manager. If you have not read the following paper, I recommend
it:
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
while it does not make any firm recommendation on account/password changes,
this really should default (IMO) to whatever policy is in place in the
domain. More importantly, follow the recommendation of using a "service"
account that is not local system and is not an administrator on the server.
Steve|||I guess this all depends upon your Security Policy that you have in place.
The service accounts should be running under a low privilege domain acount
and should be changed
based upon the password expiration you've set for other domain accounts.
So, yes I believe they should be changed.
Typically, you modify the account within SEM, but here's an article on how
to do it outside of SEM.
283811 How to change the SQL Server or SQL Server Agent Service account
without
http://support.microsoft.com/?id=283811
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
domain service account password? I assume you do not want to leave these
the same forever after install, and I have a service pwd change utility
without restarting SQL server, but not seeing a lot of talk about changing
these account(s). Should they not change just like any other priveledged
user?"Brian" <bpollard@.idahodba.org> wrote in message
news:OCqXZ8KdEHA.3016@.tk2msftngp13.phx.gbl...
> Are there any best practices recommending changing the SQL Server/Agent
> domain service account password? I assume you do not want to leave these
> the same forever after install, and I have a service pwd change utility
> without restarting SQL server, but not seeing a lot of talk about changing
> these account(s). Should they not change just like any other priveledged
> user?
>
If you change these accounts it is probably easiest to do so through
Enterprise Manager. If you have not read the following paper, I recommend
it:
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
while it does not make any firm recommendation on account/password changes,
this really should default (IMO) to whatever policy is in place in the
domain. More importantly, follow the recommendation of using a "service"
account that is not local system and is not an administrator on the server.
Steve|||I guess this all depends upon your Security Policy that you have in place.
The service accounts should be running under a low privilege domain acount
and should be changed
based upon the password expiration you've set for other domain accounts.
So, yes I believe they should be changed.
Typically, you modify the account within SEM, but here's an article on how
to do it outside of SEM.
283811 How to change the SQL Server or SQL Server Agent Service account
without
http://support.microsoft.com/?id=283811
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Changing SQL Account and Password
Hello, about 3 weeks ago I went about changing the service startup account and password for the 2 SQL Service Accounts (MSSQL & SQLAgent) using the Service applet instead of the Enterprise Manager. I made a mistake in that I forgot to ensure 2 rights (Replace a Process Level Token & Lock Pages in Memory) when I made the change but the services were still able to startup and they ran for about 1 week. I have since changed the service startup account the proper way by dropping the account and adding it again via Enterprise Manager. I checked and all the rights, etc. that Microsoft requires are there. Lately our SQL 2000 Cluster has been acting up during peak periods (the CPU goes to 100%) and our site stops responding. This did not happen that much prior to this change. My question is: is there anyway what I did initially could've caused damage to my SQL cluster? Any way to fix it? thanks for any help.i don't think cpu spiking is directly (or indirectly) related to changes you made.
Sunday, March 11, 2012
changing service account as per kb 283811
Has anybody been able to change a service account following this article:
http://support.microsoft.com/kb/q283811/
In particular,
- SQL2k0 SP4,
- non-administrative, i.e. plain-vanilla User Group, account,
- MSDE SP4 named instance, or
- named instance,
Thank you.
hi bill,
bill tie wrote:
> Has anybody been able to change a service account following this
> article: http://support.microsoft.com/kb/q283811/
> In particular,
> - SQL2k0 SP4,
> - non-administrative, i.e. plain-vanilla User Group, account,
> - MSDE SP4 named instance, or
> - named instance,
> Thank you.
283811 has not been updated for changes in sp4... I'm still trying to figure
it out what is missing...
so far I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
http://support.microsoft.com/kb/q283811/
In particular,
- SQL2k0 SP4,
- non-administrative, i.e. plain-vanilla User Group, account,
- MSDE SP4 named instance, or
- named instance,
Thank you.
hi bill,
bill tie wrote:
> Has anybody been able to change a service account following this
> article: http://support.microsoft.com/kb/q283811/
> In particular,
> - SQL2k0 SP4,
> - non-administrative, i.e. plain-vanilla User Group, account,
> - MSDE SP4 named instance, or
> - named instance,
> Thank you.
283811 has not been updated for changes in sp4... I'm still trying to figure
it out what is missing...
so far I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Changing Service Account
We need to change the account that the ReportServer service runs under. Is
it as simple as changing the account that the service logs on with, or is it
more involved?It is more involved because the RS symmetric key is encrypted and stored in
the RS database using the user that the RS Windows service is running under.
If you change the Windows service account RS won't be able to decrypt this
key unless you follow the procedure below.
1. Delete the references to the old key: rskeymgmt -r <installation id> (the
installation ID can be found in the rsreportserver.config file
2. Stop IIS and the Report Server windows service
3. Change the account service runs under to a domain account that can log in
to your reporting database.
4. Start IIS and the Report Server windows service
5. Reapply the encryption key: rskeymgmt -a -f <filename from step
1> -p<password from step 1>.
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:45676F56-51C9-45D7-9067-D289DE2D91AF@.microsoft.com...
> We need to change the account that the ReportServer service runs under.
Is
> it as simple as changing the account that the service logs on with, or is
it
> more involved?
it as simple as changing the account that the service logs on with, or is it
more involved?It is more involved because the RS symmetric key is encrypted and stored in
the RS database using the user that the RS Windows service is running under.
If you change the Windows service account RS won't be able to decrypt this
key unless you follow the procedure below.
1. Delete the references to the old key: rskeymgmt -r <installation id> (the
installation ID can be found in the rsreportserver.config file
2. Stop IIS and the Report Server windows service
3. Change the account service runs under to a domain account that can log in
to your reporting database.
4. Start IIS and the Report Server windows service
5. Reapply the encryption key: rskeymgmt -a -f <filename from step
1> -p<password from step 1>.
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:45676F56-51C9-45D7-9067-D289DE2D91AF@.microsoft.com...
> We need to change the account that the ReportServer service runs under.
Is
> it as simple as changing the account that the service logs on with, or is
it
> more involved?
Subscribe to:
Posts (Atom)