Changing the SQL Server Service Account

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

changing the server used by a report

hopefully have better luck with this.

I have two servers running the same database for two locations, I have a site setup to report for one of them and I want to make it report for both based on what location a person is in.

I have it working when there are no parameters, however as soon as you enter parameters into the mix it breaks.

here is what I use to do it when no parameters are required.

CrystalReportSource1.ReportDocument.DataSourceConnections[0].SetConnection(myConnectionInfo.ServerName, myConnectionInfo.DatabaseName, false);

CrystalReportSource1.ReportDocument.DataSourceConnections[0].SetLogon(myConnectionInfo.UserID, myConnectionInfo.Password);

I tried to put this in various locations, before the parameters are read, after reading but before setting, after everything.
Even before the report is loaded!

all gave me an error on the report.

I also tried to grab the parameter definitions, then set these and then copy the definitions back and that failed, it was read only.

Any ideas?

ThanksNM - threw in a verifydatabase on the report document and all is well.

Changing the port that Reporting Services 2000 is running on after activation.

Hi,

I have just installed reporting services 2000 on a non-standard port and have used the rsactivate tool to activate it.

I then installed Microsoft CRM which seems not to like the non-standard port for reporting services, how do I go about deactivating the webservice so I can change the port and then activate it again?

Thanks

I am not too familiar with RS2000, but I think all you have to do is to change the port in IIS, then change the url in the report server and report manager config files. You do not need to run reactivate again.

Changing the name of a SQL 7.0 Server

What is the correct way to change the name of server
running SQL 7.0?
Changing the name and running setup seems to work but does
that cause other problems?You also need to run the following after running setup
use master
go
exec sp_dropserver 'old name'
go
exec sp_addserver 'new name','local'
If SQL Server is configured to listen on either the Multiprotocol or NWLink
IPX/SPX network libraries, you'll need to remove them, and then add them
back using the SQL Server Network Utility.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...

quote:

---

> What is the correct way to change the name of server
> running SQL 7.0?
> Changing the name and running setup seems to work but does
> that cause other problems?

---

|||Hi,
For SQL 7 , after changing the server name , you need to ,
1. Run the SQL 7 setup , it automatically recognise the server name change
and will update the registry
2. Start SQL server and then login to Query analyser and execute
a. sp_dropserver <oldserver name>
b. sp_addserver <newserver name>,LOCAL
3. Restart SQL server service
Thanks
Hari
MCDBA
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...

quote:

---

> What is the correct way to change the name of server
> running SQL 7.0?
> Changing the name and running setup seems to work but does
> that cause other problems?

---

|||The procedure is explained here:
http://vyaskn.tripod.com/administration_faq.htm#q5
You'll have to amend all applications, so that they connect with the correct
name. Do you have replication in place?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
What is the correct way to change the name of server
running SQL 7.0?
Changing the name and running setup seems to work but does
that cause other problems?|||Thanks for the reply.
Named pipes is the only protocol used.

quote:

---

>--Original Message--
>You also need to run the following after running setup
>use master
>go
>exec sp_dropserver 'old name'
>go
>exec sp_addserver 'new name','local'
>If SQL Server is configured to listen on either the

---

Multiprotocol or NWLink

quote:

---

>IPX/SPX network libraries, you'll need to remove them,

---

and then add them

quote:

---

>back using the SQL Server Network Utility.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Andrew Buschbom" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
does[QUOTE]
>
>.
>

---

Changing the name of a SQL 7.0 Server

What is the correct way to change the name of server
running SQL 7.0?
Changing the name and running setup seems to work but does
that cause other problems?You also need to run the following after running setup
use master
go
exec sp_dropserver 'old name'
go
exec sp_addserver 'new name','local'
If SQL Server is configured to listen on either the Multiprotocol or NWLink
IPX/SPX network libraries, you'll need to remove them, and then add them
back using the SQL Server Network Utility.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
> What is the correct way to change the name of server
> running SQL 7.0?
> Changing the name and running setup seems to work but does
> that cause other problems?|||Hi,
For SQL 7 , after changing the server name , you need to ,
1. Run the SQL 7 setup , it automatically recognise the server name change
and will update the registry
2. Start SQL server and then login to Query analyser and execute
a. sp_dropserver <oldserver name>
b. sp_addserver <newserver name>,LOCAL
3. Restart SQL server service
Thanks
Hari
MCDBA
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
> What is the correct way to change the name of server
> running SQL 7.0?
> Changing the name and running setup seems to work but does
> that cause other problems?|||The procedure is explained here:
http://vyaskn.tripod.com/administration_faq.htm#q5
You'll have to amend all applications, so that they connect with the correct
name. Do you have replication in place?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew Buschbom" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
What is the correct way to change the name of server
running SQL 7.0?
Changing the name and running setup seems to work but does
that cause other problems?|||Thanks for the reply.
Named pipes is the only protocol used.
>--Original Message--
>You also need to run the following after running setup
>use master
>go
>exec sp_dropserver 'old name'
>go
>exec sp_addserver 'new name','local'
>If SQL Server is configured to listen on either the
Multiprotocol or NWLink
>IPX/SPX network libraries, you'll need to remove them,
and then add them
>back using the SQL Server Network Utility.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Andrew Buschbom" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d9401c3dab8$e0ec3540$a401280a@.phx.gbl...
>> What is the correct way to change the name of server
>> running SQL 7.0?
>> Changing the name and running setup seems to work but
does
>> that cause other problems?
>
>.
>

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

Changing the LoginName of a user

I'm trying to automate the creation of a databse and a User/Login but i'm running into problem with a conflict between a new user to which i'm trying to assign a WindowsLogin which is already linked to the dbo. (SQL Server 2005)

Is there a way to reassign the dbo's LoginName to some othe user

Here the code

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ABC')
BEGIN
declare @.cmd nvarchar(max)
set @.cmd = 'CREATE USER [ABC] FOR LOGIN ' + @.WindowsLogin + ' WITH DEFAULT_SCHEMA=[Shared]'
exec(@.cmd)
END

Which give me the error

Msg 15063, Level 16, State 1, Server SR-DEV-GOI1, Line 1
The login already has an account under a different user name.

The script is running under the @.WindowsLogin in question !

Thanks All

Gilbert

** Reply to myself **

I have found the way to change the loginName of the dbo user with sp_changedbowner but then it restrict the access to the database. We're gonna use an other Login to create the process (which makes more sense)
|||

The system SP sp_changedbowner uses the ALTER AUTHORIZATION DDL among other things. You can use the DDL instead to perform more granular operations including changing database ownership. See BOL for more details.

Changing the Domain User Password

Is there an automatic way of changing the Domain user password getting used for running the SQL Server as a Domain user account? I'm taking about EM--Security--Domain User name and the password getting used for running the SQL Server?Check out this thread

http://www.dbforums.com/t992825.html

changing the data files to another disk partition

Hi,

I have a sql server 7 running on a machine with two disk partitions (D: and E:).

The data files xxx.mdf and xxx.ldf are stored in D:, which has very few space available. I want to copy these files to E: but I get an error saying that it is not possible to change the source file of a database. Is it possible to do it or do i have to create another data file in E: and keep the old one in D:?

Thanks in advance,

browsersp_detach_db + sp_attach_db ... with (...)|||detach the database ... copy to new location ... then attach

or

backup the database ... restore ... sp_renamedb|||thanks ms_sql_dba and Enigma, it worked properly!sql

changing TCP/IP port breaks "local" connection

Hi All:
I wanted to change the TCP/IP port a default instance of SQL was
running on (from 1433 to something random like 49576) on Win2K AS
(sp4+). SQL's got TCP/IP and NamedPipes libraries active. I believe
MDAC 2.8 is installed.
The server has a base IP of 192.168.1.190 and a website running on
192.168.1.191. The website uses a SQL0LEDB.1 connection to the
database using "local" for the server name.
This all runs fine when the SQL server's on 1433, but if I change it
to another port, it breaks the connection from the website to the SQL
server. Restarting services doesn't help, even rebooting doesn't
restore the connection. If I switch the SQL Server back to 1433 it
runs fine.
I tested the same thing on a server running Win2003, and the website
didn't have a problem connecting to the SQL server when I changed the
TCP/IP port.
Any ideas why this breaks on Win2K?
TIAcheck 1434
and
delete
HKLM\software\microsoft\mssqlserver\client\supersocketnetlib\lastconnect key
<sql server name or ip>
"vze78se7@.verizon.net" wrote:
> Hi All:
> I wanted to change the TCP/IP port a default instance of SQL was
> running on (from 1433 to something random like 49576) on Win2K AS
> (sp4+). SQL's got TCP/IP and NamedPipes libraries active. I believe
> MDAC 2.8 is installed.
> The server has a base IP of 192.168.1.190 and a website running on
> 192.168.1.191. The website uses a SQL0LEDB.1 connection to the
> database using "local" for the server name.
> This all runs fine when the SQL server's on 1433, but if I change it
> to another port, it breaks the connection from the website to the SQL
> server. Restarting services doesn't help, even rebooting doesn't
> restore the connection. If I switch the SQL Server back to 1433 it
> runs fine.
> I tested the same thing on a server running Win2003, and the website
> didn't have a problem connecting to the SQL server when I changed the
> TCP/IP port.
> Any ideas why this breaks on Win2K?
> TIA
>|||On Mon, 24 Jan 2005 06:05:04 -0800, "Aleksandar Grbic"
<AleksandarGrbic@.discussions.microsoft.com> wrote:
>check 1434
Thanks, Alexsandar. What do you mean by "check 1434"? I have TCP/UDP
1434 disabled because I don't want to broadcast or receive on the
"slammer" port.
>and
>delete
>HKLM\software\microsoft\mssqlserver\client\supersocketnetlib\lastconnect key
><sql server name or ip>
I will try that, thanks.
>"vze78se7@.verizon.net" wrote:
>> Hi All:
>> I wanted to change the TCP/IP port a default instance of SQL was
>> running on (from 1433 to something random like 49576) on Win2K AS
>> (sp4+). SQL's got TCP/IP and NamedPipes libraries active. I believe
>> MDAC 2.8 is installed.
>> The server has a base IP of 192.168.1.190 and a website running on
>> 192.168.1.191. The website uses a SQL0LEDB.1 connection to the
>> database using "local" for the server name.
>> This all runs fine when the SQL server's on 1433, but if I change it
>> to another port, it breaks the connection from the website to the SQL
>> server. Restarting services doesn't help, even rebooting doesn't
>> restore the connection. If I switch the SQL Server back to 1433 it
>> runs fine.
>> I tested the same thing on a server running Win2003, and the website
>> didn't have a problem connecting to the SQL server when I changed the
>> TCP/IP port.
>> Any ideas why this breaks on Win2K?
>> TIA|||>check 1434
>and
>delete
>HKLM\software\microsoft\mssqlserver\client\supersocketnetlib\lastconnect key
><sql server name or ip>
>
One other question...Can I avoid this problem by using Named Pipes?
I will only ever be connecting to the local machine from this
website. I thought using "(local)" for the server bypassed TCP/IP
altogether?
Or does this problem have nothing to do with TCP/IP?|||configure alias in client network utility on client
make alias for sql server in alias tab, write sql server alias, sql name and
tcp/ip port
or
make alias with named pipe
"vze78se7@.verizon.net" wrote:
> >check 1434
> >and
> >delete
> >HKLM\software\microsoft\mssqlserver\client\supersocketnetlib\lastconnect key
> ><sql server name or ip>
> >
> One other question...Can I avoid this problem by using Named Pipes?
> I will only ever be connecting to the local machine from this
> website. I thought using "(local)" for the server bypassed TCP/IP
> altogether?
> Or does this problem have nothing to do with TCP/IP?
>sql

Changing SQL startup account

I just can't seem to get this working right. I want to make it just a
regular domain user in a W2k domain on a 2003 server running sql 2000 sp3.
Everything is in place from what I see. It actually works too...until you
reboot. I don't konw what is wrong. Perhaps someone knows about this
error.
SQL Server could not find the default instance (MSSQLSERVER) - please
specify the name of an existing instance on the invocation of sqlservr.exe.
I think I reinstalled this about 10 times now, I'll do it again if it makes
it work too.
Yes, I get the same error loging in as the accound and trying to run the
sqlservr manually.
I'm guessing it is something from one of the policies when it reboots?
Or maybe the service start after the initial install/change in the
Enterprise Manager and the service starts out of context?
Any help from someone who did this before would be great. Because at this
point I don't even think it will work.Check the NT Application Event logs for errors while SQL is starting.
May be problems with the account you're using to start the service with or
if you've changed /moved database files
to another drive location etc.
Also, check the sqlstp.log for errors as well during initial setup.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Ok,
I think I'm on the right track here, It looks like the registry
permissions are the ones at fault. Regmon proves useful again. I can go
and set the permissions manually on the following keys:
HKLM\SOFTWARE\Microsoft\MSSQLSERVER\MSSQ
LServer\CurrentVersion
HKLM\SOFTWARE\Microsoft\MSSQLSERVER\\Set
up
This I have done previously and SEM does this too.
Of course I can set the permissions, but... they disappear after I restart
the sql service. That is the only access denied message in regmon. The
ntfs permissions are still correct, just the registry. Has anyone run into
this before? I never came across permissions in the registry being modified
by any application that was not being setup.
And now I have, setting the permissions on the registry keys while the
cluster service was controlling the service did not properly replicate over
to the corresponding cluster server. Thus the permissions being reverted
after setting them while the service was in a "offline" state...created a
difference which was overridden by the cluster service's replication.
Why did this happen? I'm sure anyone in the group can tell me why this
occurred. And I'm gonna leave this out.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:0DSXEPtGFHA.2412@.TK2MSFTNGXA02.phx.gbl...
> Check the NT Application Event logs for errors while SQL is starting.
> May be problems with the account you're using to start the service with or
> if you've changed /moved database files
> to another drive location etc.
> Also, check the sqlstp.log for errors as well during initial setup.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||You failed to mention this was a SQL Cluster in your original post.
You can't make manual changes to the SQL registry keys as the Windows
Cluster checkpoint will just roll back the changes
you made manually.
It sounds like you have both a non-Clustered default instance and a
Clustered instance.
My advice would be to open a case with PSS SQL Support to resolve this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Luke,
We had a very similar situation, and we followed the solution in:
http://groups.google.com/groups?q=l...ftngxa07&rnum=3
However, we weren't using clusters so this might not be appropriate.
Simon.
"Luke" wrote:

> I just can't seem to get this working right. I want to make it just a
> regular domain user in a W2k domain on a 2003 server running sql 2000 sp3.
> Everything is in place from what I see. It actually works too...until you
> reboot. I don't konw what is wrong. Perhaps someone knows about this
> error.
> SQL Server could not find the default instance (MSSQLSERVER) - please
> specify the name of an existing instance on the invocation of sqlservr.exe
.
> I think I reinstalled this about 10 times now, I'll do it again if it make
s
> it work too.
> Yes, I get the same error loging in as the accound and trying to run the
> sqlservr manually.
> I'm guessing it is something from one of the policies when it reboots?
> Or maybe the service start after the initial install/change in the
> Enterprise Manager and the service starts out of context?
> Any help from someone who did this before would be great. Because at this
> point I don't even think it will work.
>
>sql

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:

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

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
>
>

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.

Changing SQL Server instance name

Hi

Is it possible to change the name of a SQL Server instance on a server running multiple server instances? Or must the server be reinstalled?

Rgds

Bertrand

Hi,

Unfortunatelly, I do not know any solution for renaming instance of SQL server. It is not possible. Try to reinstall with the new instance name of your sql server.

Regards,

Janos

|||There is NO way to do this.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Changing SQL Server configuration in a cluster environment

I running SQL Server on a Windows active passive cluster and need to change
SQL Server configuration. If I change the configuration thru the virtual ser
ver the change is applied to the active node what happens to the passive nod
e? Is it necessary for me t
o failover and apply the change there too?Hi
Most configuration settings are stored in Master, which is shared.
If is recommended to fail over to the other nodes after you make a
configuration change as a test, you don't want to find out weeks down the
line that the failover failed because of a setting that was not identical on
all nodes.
There area a few settings in the registry under
" HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer", especially
" HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer" where
settings like LoginMode, AuditLevel and ListenOn are stored.
Cheers
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
Specialist SQL Server Solutions and Consulting
MVP Program: http://www.microsoft.com/mvp
"Jerrick D.H" <JerrickDH@.discussions.microsoft.com> wrote in message
news:301F2959-3BC0-4E96-8F55-838AC4813BA3@.microsoft.com...
> I running SQL Server on a Windows active passive cluster and need to
change SQL Server configuration. If I change the configuration thru the
virtual server the change is applied to the active node what happens to the
passive node? Is it necessary for me to failover and apply the change there
too?|||Your're so right about that.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Most configuration settings are stored in Master, which is shared.
> If is recommended to fail over to the other nodes after you make a
> configuration change as a test, you don't want to find out weeks down the
> line that the failover failed because of a setting that was not identical
on
> all nodes.
> There area a few settings in the registry under
> " HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer", especially
> " HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer" where
> settings like LoginMode, AuditLevel and ListenOn are stored.
> Cheers
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Epprecht Consulting (PTY) LTD
> Johannesburg, South Africa
> Mobile: +27-82-552-0268
> IM: mike@.epprecht.net
> Specialist SQL Server Solutions and Consulting
> MVP Program: http://www.microsoft.com/mvp
> "Jerrick D.H" <JerrickDH@.discussions.microsoft.com> wrote in message
> news:301F2959-3BC0-4E96-8F55-838AC4813BA3@.microsoft.com...
> change SQL Server configuration. If I change the configuration thru the
> virtual server the change is applied to the active node what happens to th
e
> passive node? Is it necessary for me to failover and apply the change ther
e
> too?
>
>

Changing SQL Server configuration in a cluster environment

I running SQL Server on a Windows active passive cluster and need to change SQL Server configuration. If I change the configuration thru the virtual server the change is applied to the active node what happens to the passive node? Is it necessary for me t
o failover and apply the change there too?
Hi
Most configuration settings are stored in Master, which is shared.
If is recommended to fail over to the other nodes after you make a
configuration change as a test, you don't want to find out weeks down the
line that the failover failed because of a setting that was not identical on
all nodes.
There area a few settings in the registry under
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ", especially
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \MSSQLServer" where
settings like LoginMode, AuditLevel and ListenOn are stored.
Cheers
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
Specialist SQL Server Solutions and Consulting
MVP Program: http://www.microsoft.com/mvp
"Jerrick D.H" <JerrickDH@.discussions.microsoft.com> wrote in message
news:301F2959-3BC0-4E96-8F55-838AC4813BA3@.microsoft.com...
> I running SQL Server on a Windows active passive cluster and need to
change SQL Server configuration. If I change the configuration thru the
virtual server the change is applied to the active node what happens to the
passive node? Is it necessary for me to failover and apply the change there
too?
|||Your're so right about that.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Most configuration settings are stored in Master, which is shared.
> If is recommended to fail over to the other nodes after you make a
> configuration change as a test, you don't want to find out weeks down the
> line that the failover failed because of a setting that was not identical on
> all nodes.
> There area a few settings in the registry under
> "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ", especially
> "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \MSSQLServer" where
> settings like LoginMode, AuditLevel and ListenOn are stored.
> Cheers
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Epprecht Consulting (PTY) LTD
> Johannesburg, South Africa
> Mobile: +27-82-552-0268
> IM: mike@.epprecht.net
> Specialist SQL Server Solutions and Consulting
> MVP Program: http://www.microsoft.com/mvp
> "Jerrick D.H" <JerrickDH@.discussions.microsoft.com> wrote in message
> news:301F2959-3BC0-4E96-8F55-838AC4813BA3@.microsoft.com...
> change SQL Server configuration. If I change the configuration thru the
> virtual server the change is applied to the active node what happens to the
> passive node? Is it necessary for me to failover and apply the change there
> too?
>
>

Changing sql enterprise to sql standard edition.

Hi, now we have a server that is running in sql server2000 enterprise
edition. For licenses problems we need to desinstall this edition and
install the standard edition.
Do somebody know if there is a problem in attach the databases that were
created in the enterprise edition on the new standard edition server'
What do you recommend to me to do this migration?
Thanks a lot for your help!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can attach databases from EE to SE (in fact, you can attach across all editions).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"MariaGuzman" <marisa@.devdex.com> wrote in message news:uet9BdaHFHA.3376@.TK2MSFTNGP14.phx.gbl...
> Hi, now we have a server that is running in sql server2000 enterprise
> edition. For licenses problems we need to desinstall this edition and
> install the standard edition.
> Do somebody know if there is a problem in attach the databases that were
> created in the enterprise edition on the new standard edition server'
> What do you recommend to me to do this migration?
> Thanks a lot for your help!!!
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||> You can attach databases from EE to SE (in fact, you can attach across all
editions).
Well, except a > 2GB database to MSDE... :-)|||Good point, Aaron. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ejmYeIbHFHA.2456@.TK2MSFTNGP09.phx.gbl...
>> You can attach databases from EE to SE (in fact, you can attach across all
> editions).
> Well, except a > 2GB database to MSDE... :-)
>|||Functionaly, the database should be equivalent. Unless you were doing
esoteric Distributed Partitioned Views or some such, you should run into any
problems.
Take a look at the product features of both editions to see if any of these
match thing you've been doing.
Also consider that if you had purchased Developer Edition to build your
systems, you would have been using Enterprise Edition and it would have been
likely that many users have deployed such systems to production level
Standard Edition. The only difference between DE and EE is the EULA.
Sincerely,
Anthony Thomas
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uet9BdaHFHA.3376@.TK2MSFTNGP14.phx.gbl...
Hi, now we have a server that is running in sql server2000 enterprise
edition. For licenses problems we need to desinstall this edition and
install the standard edition.
Do somebody know if there is a problem in attach the databases that were
created in the enterprise edition on the new standard edition server'
What do you recommend to me to do this migration?
Thanks a lot for your help!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Changing sql enterprise to sql standard edition.

Hi, now we have a server that is running in sql server2000 enterprise
edition. For licenses problems we need to desinstall this edition and
install the standard edition.
Do somebody know if there is a problem in attach the databases that were
created in the enterprise edition on the new standard edition server'
What do you recommend to me to do this migration?
Thanks a lot for your help!!!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You can attach databases from EE to SE (in fact, you can attach across all e
ditions).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"MariaGuzman" <marisa@.devdex.com> wrote in message news:uet9BdaHFHA.3376@.TK2MSFTNGP14.phx.gb
l...
> Hi, now we have a server that is running in sql server2000 enterprise
> edition. For licenses problems we need to desinstall this edition and
> install the standard edition.
> Do somebody know if there is a problem in attach the databases that were
> created in the enterprise edition on the new standard edition server'
> What do you recommend to me to do this migration?
> Thanks a lot for your help!!!
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||> You can attach databases from EE to SE (in fact, you can attach across all
editions).
Well, except a > 2GB database to MSDE... :-)|||Good point, Aaron. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ejmYeIbHFHA.2456@.TK2MSFTNGP09.phx.gbl...
> editions).
> Well, except a > 2GB database to MSDE... :-)
>|||Functionaly, the database should be equivalent. Unless you were doing
esoteric Distributed Partitioned Views or some such, you should run into any
problems.
Take a look at the product features of both editions to see if any of these
match thing you've been doing.
Also consider that if you had purchased Developer Edition to build your
systems, you would have been using Enterprise Edition and it would have been
likely that many users have deployed such systems to production level
Standard Edition. The only difference between DE and EE is the EULA.
Sincerely,
Anthony Thomas
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uet9BdaHFHA.3376@.TK2MSFTNGP14.phx.gbl...
Hi, now we have a server that is running in sql server2000 enterprise
edition. For licenses problems we need to desinstall this edition and
install the standard edition.
Do somebody know if there is a problem in attach the databases that were
created in the enterprise edition on the new standard edition server'
What do you recommend to me to do this migration?
Thanks a lot for your help!!!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!