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 sa password of publisher and subscriber

Hi,

I have publish the data which have two subscriber ,one is in local network and other one is through internet.

Now As per compnay policy i am going to change tha sa password of Publisher and subscriber.

So i want know that it will effect replication or not.

if it will then what should i do to change the sa password.

Regards

Sanjay Tiwari

Depending on how you setup your repl. I suggest you take a look at Vyas' article.

http://vyaskn.tripod.com/repl_ans4.htmsql

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 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 (local) SQL Server registration name

Hi,
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.
You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registration and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:

> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>
|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.

>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>.
>
|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regidter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:

> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> servername or ..
> delete the registration and add one with the servername.
> sp_addserver.
>

Changing the (local) SQL Server registration name

Hi,
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registration and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:
> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.
>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>> Hi,
>> When we install SQL server, it creates a (local) SQL
>> Server registration. How can we change this name from
>> (local) to computer's name?
>> Thank you.
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regidter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:
> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> >--Original Message--
> >You should be able to access the server via (local),
> servername or ..
> >If you are talking about enterprise manager then just
> delete the registration and add one with the servername.
> >
> >Check @.@.server_name you may have to add it via
> sp_addserver.
> >
> >"anonymous@.discussions.microsoft.com" wrote:
> >
> >> Hi,
> >>
> >> When we install SQL server, it creates a (local) SQL
> >> Server registration. How can we change this name from
> >> (local) to computer's name?
> >>
> >> Thank you.
> >>
> >.
> >
>

Changing the (local) SQL Server registration name

Hi,
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registratio
n and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:

> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.

>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running
on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regi
dter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:

> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> servername or ..
> delete the registration and add one with the servername.
> sp_addserver.
>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?
TIA
check 1434
and
delete
HKLM\software\microsoft\mssqlserver\client\superso cketnetlib\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\supers ocketnetlib\lastconnect key
><sql server name or ip>
I will try that, thanks.
[vbcol=seagreen]
>"vze78se7@.verizon.net" wrote:
|||
>check 1434
>and
>delete
>HKLM\software\microsoft\mssqlserver\client\supers ocketnetlib\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:

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

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 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\clie
nt\supersocketnetlib\lastconnect ke
y
<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\clie
nt\supersocketnetlib\lastconnect k
ey
><sql server name or ip>
I will try that, thanks.
[vbcol=seagreen]
>"vze78se7@.verizon.net" wrote:
>|||
>check 1434
>and
>delete
> HKLM\software\microsoft\mssqlserver\clie
nt\supersocketnetlib\lastconnect k
ey
><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:

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

Changing SSRS reports datasource.

I have a web site that has reports stored in two places.The local site, and in SSRS.The local reports get there data from a class that has a dataset method on it.I would like to store all my reports in SSRS.Is this possible?Can I get just the RDL of a report from the SSRS web service, or change the dataset to what my class generates?

You can adopt one to two approaches:

1) store the RDLC you'll use in a report viewer control on the report server as a resouce.

2) store actual RDL in the report server and use the GetReportDefinition method to obtain the RDL.

What is interesting here is that the data source definitions between the RDLC the control expect and the RDL that the server expects is a little different.

Here's the reference for GetReportDefinition:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportdefinition.aspx

-Lukasz

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 Logon Config.

Hello,

When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:

A connection could not be established to (LOCAL)

Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..

Please verify SQL Server is running and check your SQL Server registration properties and try again.

I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)

Thank youI always use a dedicated domain account for many reasons. exchange integration, access to file servers etc... and you should set that domain account to never have a password that never expires.|||... and you should set that domain account to never have a password that never expires.I suspect that you might have "over nevered" here. I would want the domain account to have a password that never expired (or I'd create a single application to change the service passwords and the AD password, all in one swell foop).

-PatP|||I'm afraid I don't quite get what you're saying here. The domain login is to stay the same in this case. It is the local account that the sql server installation is tied to that must have its password altered. The problem is that upon altering the password in Windows, SQL server will not start up the instance, giving that invalid logon message. Sorry to say that I'm still at a loss here. :confused:

Changing Query behavior based on local vs. remote context?

Hello,
I'm not sure if this is the right ng for this question, but here goes...
I just installed a product that uses SQL Express. When I run queries
against the installed instance I get different behavior depending on if the
query is run from Management Studio locally or from SSMS running on a remote
machine. In both cases the user is a domain admin and the query is
identical. The local query returns 1000+ rows, the remote query returns
ZERO. If the query returns less than say 100 rows, there is no difference
between remote and local.
Has anyone else seen this or use use/implemented it themselves?
I'm curious; how this behavior is defined/implemented? Is it database or
instance specific? It seems to be instance-wide in my case but I'm not sure
yet. I've been digging around in SSMS but haven't found any settings or
properties that seem to apply to this, so any sugestions on where to look
would be appreciated.
Thanks!
KeithMy guess would be that there is a problem on the remote machine. Have you
tried a 2nd remote query?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||Yes...a dozen or so actually. And I've tried them on different remote
machines as well with SSMS and SSMSe, in addition to using queries from the
query window and "Open Table/View" from the Object Explorer.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> My guess would be that there is a problem on the remote machine. Have you
> tried a 2nd remote query?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Keith" <keith@.alh.com> wrote in message
> news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like larger network packets are getting dropped. Some bridges that
do not do packet splitting can cause this.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||OK then I would have to agree with Geoff in that it sounds like an issue
with the servers communications. If you can run this fine locally and all
other machines have issues it kind of narrows it down to that machine.
Check the event logs and see if there are errors on that server.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:e8xPg6C4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Yes...a dozen or so actually. And I've tried them on different remote
> machines as well with SSMS and SSMSe, in addition to using queries from
> the query window and "Open Table/View" from the Object Explorer.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
>|||Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
Keith's description reminds me of a very weird error that a formed DSL
provider of mine had. I was mainly reading my after a holiday in SSH2
connection to a Unix account. And that worked fine. But then I got the
idea to look at some web site, but I could not access it. Tried another.
Did not work. A third one. Eventually I tried running lynx from the Unix
account. And that hung too! But I could open a new connection and read my
mail.
Finally I came around to put a very short text file on my web site, and
sure enough, this page did turn up in the browser. So I concluded they
had an error where split packets got lost, but as long as the packets
were small, things worked.
(The reason this provider is a former provider, is simply because I moved
to a new flat, and they could not deliver DSL there.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:erXXwEE4HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
I should add that this behavior does not happen running queries against any
other SQL Express, SQL 2005, SQL 2000, or MSDE instance - named or
otherwise, physical or virtual machine - anywhere on my network.
Only this particular instance behaves this way.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> OK then I would have to agree with Geoff in that it sounds like an issue
> with the servers communications. If you can run this fine locally and all
> other machines have issues it kind of narrows it down to that machine.
Unless you install the product on another machine and get the same behavior,
which I have.

> Check the event logs and see if there are errors on that server.
The Event Viewer does not show any errors, nor does the error log in the
instance's data directory.
Considering that this behavior is present along with the installation of
this particular product, and that other named SQL and SQL Express instances
on the same machine do not demonstrate this behavior - even when returning
result sets orders of magnitude larger and on the same physical network -
I'm inclined to believe that this behavior is not machine specific but
rather localized to the named instance installed by this particular product.
Meaning, some configurable property of SQL Server.
I'm totally willing to believe that this is a bug in the product - a
misconfiguration of some kind - but it sounds like most folks don't think
that this type of (mis)configuration is even possible.
Oh, did I mention that this is a Microsoft product's install of SQL Express
;-)
k|||> Unless you install the product on another machine and get the same
> behavior, which I have.

> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
Tidbits like these are always good to know. The answers we gave were based
on the info given. I would ping the makers of the product then and see if
they can provide a clue. Other than some ANSI settings at the connection or
database object level that may return different results I know of no
settings or configuration that would produce this behavior. Things like ANSI
PADDING, ANSI NULLS etc can affect how SQL Server treats the data for joins
and the Where clause which can produce different result sets. But none of
that is based on the size of the result set. I would check to see if the
connection settings are the same for both the local and remote SSMS. But
like I said that doesn't explain all the symptoms you mention.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:O%23h0HtF4HHA.484@.TK2MSFTNGP06.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Unless you install the product on another machine and get the same
> behavior, which I have.
>
> The Event Viewer does not show any errors, nor does the error log in the
> instance's data directory.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
> I'm totally willing to believe that this is a bug in the product - a
> misconfiguration of some kind - but it sounds like most folks don't think
> that this type of (mis)configuration is even possible.
> Oh, did I mention that this is a Microsoft product's install of SQL
> Express ;-)
> k
>
>|||I discovered this when I was a network admin/dba many years ago. We had a
business network and a process control network that had to be splittable for
political reasons. We had an ethernet bridge set up that we could unplug to
isolate the systems. I was setting up Replication (SQL 6.0/6.5, I think)
between two servers. The both had FDDI interfaces , but there was the
ethernet bridge between them. They negotiated large frame sizes since they
were both on FDDI, not considering that the equipment in the middle couldn't
pass that big a packet. Whatever genius designed the netotiation protocol
didn't bother to test and see if an actual large packet could make it
through. Drove me buggy troubleshooting it. Test queries worked. Ping
never failed. Drives mapped. Then replication would fail to sync. I
eventually found the issue and limited the packet size on the
publisher/distributor and never had another problem.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns998F55155F70Yazorman@.127.0.0.1...
> Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Keith's description reminds me of a very weird error that a formed DSL
> provider of mine had. I was mainly reading my after a holiday in SSH2
> connection to a Unix account. And that worked fine. But then I got the
> idea to look at some web site, but I could not access it. Tried another.
> Did not work. A third one. Eventually I tried running lynx from the Unix
> account. And that hung too! But I could open a new connection and read my
> mail.
> Finally I came around to put a very short text file on my web site, and
> sure enough, this page did turn up in the browser. So I concluded they
> had an error where split packets got lost, but as long as the packets
> were small, things worked.
>
> (The reason this provider is a former provider, is simply because I moved
> to a new flat, and they could not deliver DSL there.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Changing paper size through asp

I have designed a report on my local drive to be used as a label (4" X2") I've installed the driver for the label printer and set the paper size. Works Great. I put the report on our Crystal Server where it is called through asp. Instead of getting individual 4X2 labels, I get an 8X10 with all the labels listed. Is there a way to define the paper size through the asp page? Or does the printer driver need to be installed on the server? I don't want to mess things up for our IT people and none of them know the solution. Thanks!Hi,

To set the papersize you can use,

session("oRpt").PaperSize property.

=>session("oRpt") is the Report Object

But I'm not sure about the driver. Postively it will expect all the resources in Server.

Changing Machine Names MSDE

I have a local install of MSDE on my workstation. I have also defined a
linked server with another SQL Server on our network. I accepted all the
defaults when I installed MSDE and the server got named my machine name.
Everything is working great.

Our IT department now needs to change my machine name. What effect will
this have on my current SQL installation?

Help and suggestions appreciated.

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***Frank Bishop (fbishop@.viper.com) writes:
> I have a local install of MSDE on my workstation. I have also defined a
> linked server with another SQL Server on our network. I accepted all the
> defaults when I installed MSDE and the server got named my machine name.
> Everything is working great.
> Our IT department now needs to change my machine name. What effect will
> this have on my current SQL installation?

If memory serves, the server name changes automatically. However,
@.@.servername usually gets messed up.

To this end to:

exec sp_dropserver 'oldname'
exec sp_addserver 'newname', 'local'

Then stop and restart SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing default local server

Hi!
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. Th
e
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:

> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services fr
om
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/...&DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>

Changing default local server

Hi!
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. The
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.
Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/d...DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.
|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:

> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services from
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/d...DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>