Changing the SQL Server Agent ID

Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
> > Hello,
> >
> > We have are using SQL 2000 on a 2003 server. We have one
> > instance of 2000 up to service pack 3a.
> >
> > We need to change the domain user id of the SQL Server
> > Agent. We changed it through the Services section of
> > Windows 2003. Then starting up it reports 'Access is
> > deniged'
> >
> > The domain userid we are change it to is already an
> > administrator on the server, and is used on a different
> > server, and I am logged on as a local administrator.
> >
> > Can anyone point the way ?
> >
> > Thanks
> > Peter
>sql

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

I need to change the domain account our SQL cluster is running under. The
cluster is Win2k with a single instance of SQL 2000.
I looked in the cluster Administrator and I don't see where it is specified.
Do I use the services applet in control panel to specify the user for
cluster service? I found for SQL in Enterprise manager I can specify the
user there. Is that where I change the SQL user?
I'm thinking...
- Change the user for SQL in Enterprise Manager
- Stop SQL and the Cluster Service
- Change the user for Cluster Service in Control Panel
- Restart Cluster Service
- Restart SQL
Would this be right? Thanks in advance.
You are in luck. Microsoft has a document detailing exactly how to do this.
How to change service accounts for a clustered SQL Server computer
http://support.microsoft.com/kb/239885/en-us
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sam" <sam@.nospam.com> wrote in message
news:AqaZf.3719$i41.904@.newsread1.news.atl.earthli nk.net...
>I need to change the domain account our SQL cluster is running under. The
> cluster is Win2k with a single instance of SQL 2000.
> I looked in the cluster Administrator and I don't see where it is
> specified.
> Do I use the services applet in control panel to specify the user for
> cluster service? I found for SQL in Enterprise manager I can specify the
> user there. Is that where I change the SQL user?
> I'm thinking...
> - Change the user for SQL in Enterprise Manager
> - Stop SQL and the Cluster Service
> - Change the user for Cluster Service in Control Panel
> - Restart Cluster Service
> - Restart SQL
> Would this be right? Thanks in advance.
>
>
|||Thanks for the link. I'm trying to verify if the policies specified in the
article are indeed set for the new account we plan to use. We have already
setup a new Win_2003/SQL_2005 cluster with the new account so my guess it
the account is ok and ready to go.
The article doesn't really give a process however, other than making sure
the change to the SQL service is made with Enterprise Manager. I'm still
not sure where to change the cluster service account, except to guess about
using the Services applet in Control Panel. Is this right?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23$u90$YWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> You are in luck. Microsoft has a document detailing exactly how to do
this.[vbcol=seagreen]
> How to change service accounts for a clustered SQL Server computer
> http://support.microsoft.com/kb/239885/en-us
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Sam" <sam@.nospam.com> wrote in message
> news:AqaZf.3719$i41.904@.newsread1.news.atl.earthli nk.net...
The[vbcol=seagreen]
the
>
|||Enterprise Manager lets you change the SQL service account directly. Do not
use the services applet to change account information in a clustered
environment. EM sets the account properties correctly on each node when you
make the change. EM is also cluster-aware and changes all the nodes at
once.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sam" <sam@.nospam.com> wrote in message
news:JzbZf.1275$Es3.1066@.newsread3.news.atl.earthl ink.net...
> Thanks for the link. I'm trying to verify if the policies specified in
> the
> article are indeed set for the new account we plan to use. We have
> already
> setup a new Win_2003/SQL_2005 cluster with the new account so my guess it
> the account is ok and ready to go.
> The article doesn't really give a process however, other than making sure
> the change to the SQL service is made with Enterprise Manager. I'm still
> not sure where to change the cluster service account, except to guess
> about
> using the Services applet in Control Panel. Is this right?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23$u90$YWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> this.
> The
> the
>

Changing security authentication type.

Hi,
in an existing instance of SQL, will changing the security authentication
from SQL & Windows logins, to only windows logins cause any issues?
Also, if a database has been created with hardly any security already, are
there any issues with introducing security further down the line. For
example, a SQL server has been deployed already by my predecessor, with
authentication in SA & windows mode, and allowing pretty much anyone access
to SQL. If i was to create a DBA_Admin group and assign admin writes only to
that group, will it cause any problems in a already functioning database?
TIAInline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi,
> in an existing instance of SQL, will changing the security authentication
> from SQL & Windows logins, to only windows logins cause any issues?
>
It will block (lock out) any attempts to access the database using the sa
account (or any other SQL Login).

> Also, if a database has been created with hardly any security already, are
> there any issues with introducing security further down the line. For
There 'could' be substaintial disruption when you introduce security.
Consider a building where there were no keys necessary to enter the building
and the rooms, and suddenly keys were required to enter the building, and
also to enter rooms. Until everyone got all of the correct keys for their
needs, there would be major disruption. However, if this was a well planned
process, and appropraite keys were distributed before all the locks were
install, the disruption would be minimal if at all.

> example, a SQL server has been deployed already by my predecessor, with
> authentication in SA & windows mode, and allowing pretty much anyone
> access to SQL. If i was to create a DBA_Admin group and assign admin
> writes only to that group, will it cause any problems in a already
> functioning database?
Creating a domain/DBA_Admin group, providing that group login access to the
server, and also placing that group in the sysadmin server role, will not
cause any problems in a functioning database.
The problems will occur as you start locking down and removing permissions
from the sa account. And you 'should' do that. Applications should not be
using the sa account for database access since the sa account can do
'anything' with and to the server.

> TIA
>|||ok, many thanks for the reply.
So in short you're saying to keep the "mixed" authentication, but to secure
the server down, the best path to take is to create an AD DBA group, and
configure key users to be in that group?
furthermore, to leave the sa account's permissions well alone!
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Inline...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:%23E25Hpb1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It will block (lock out) any attempts to access the database using the sa
> account (or any other SQL Login).
>
>
> There 'could' be substaintial disruption when you introduce security.
> Consider a building where there were no keys necessary to enter the
> building and the rooms, and suddenly keys were required to enter the
> building, and also to enter rooms. Until everyone got all of the correct
> keys for their needs, there would be major disruption. However, if this
> was a well planned process, and appropraite keys were distributed before
> all the locks were install, the disruption would be minimal if at all.
>
> Creating a domain/DBA_Admin group, providing that group login access to
> the server, and also placing that group in the sysadmin server role, will
> not cause any problems in a functioning database.
> The problems will occur as you start locking down and removing permissions
> from the sa account. And you 'should' do that. Applications should not be
> using the sa account for database access since the sa account can do
> 'anything' with and to the server.
>
>|||Actually, I recommend that you take a deliberative approach to removing all
usage of the sa account, possibly with the eventual goal of switching to
Windows authentication.
Your first steps, however, are to determine the impact on the applications
of changing the connection strings. For some applications, that will be
relatively easy, for others, a re-deploy may be required and that will take
some time, effort and coordination.
Create one or more logins for the applications. Create one or more roles for
each database. Give those roles access to the appropriate databases. give
those roles permissions to the tables, views, stored procedures and
functions required by the applications. Place the logins in the new roles.
DO NOT put these logins in the sysadmin role.
Change the connections strings for the applications to use the new logins.
You want to eventually remove any application usage of the sa account.
This is a goal, it may take some time to eventually put it into place.
But for now, take no action to change the sa account -until you understand
the implications, and have a plan to addresses any issues.
However, as long as the sa account password is readily known by developers
(and whomever else may know it), you really have no security for your
database. (And if the application is hacked, the hackers will have admin
privileges in the database.)
There may be other regulatory reasons that you need to tighten up the
security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
likely be flagged as a security audit failure.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> ok, many thanks for the reply.
> So in short you're saying to keep the "mixed" authentication, but to
> secure the server down, the best path to take is to create an AD DBA
> group, and configure key users to be in that group?
> furthermore, to leave the sa account's permissions well alone!
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23tasa7b1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||Many thanks for your advice to date Arnie. Could i just run some more
questions past the group.
After reading your reply, i'm thinking that yes in time the sa account will
be withdrawn from use.
I'm just trying to get my head around a plan of attack on how it will take
place.
The first step i want to take is to create a group for suitable DBA's to
administer the server.
I'm guessing that i'm also going to have to create a DBA Admin user, which
will effectively take the place of the SA account, which will not as you
mention be a member of the sysadmin group / role.
Is the switch over just as easy as selecting the windows authentication
radio button?
if so, if the changeover is made, if things go wrong, will the role back be
to retick mixed mode or would the initial changeover be not possible to
reverse?
also what changes will need to be done to the individual databases, just
changing the connection strings?
once again, thanks for any further advice.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
> Actually, I recommend that you take a deliberative approach to removing
> all usage of the sa account, possibly with the eventual goal of switching
> to Windows authentication.
> Your first steps, however, are to determine the impact on the applications
> of changing the connection strings. For some applications, that will be
> relatively easy, for others, a re-deploy may be required and that will
> take some time, effort and coordination.
> Create one or more logins for the applications. Create one or more roles
> for each database. Give those roles access to the appropriate databases.
> give those roles permissions to the tables, views, stored procedures and
> functions required by the applications. Place the logins in the new roles.
> DO NOT put these logins in the sysadmin role.
> Change the connections strings for the applications to use the new logins.
> You want to eventually remove any application usage of the sa account.
> This is a goal, it may take some time to eventually put it into place.
> But for now, take no action to change the sa account -until you understand
> the implications, and have a plan to addresses any issues.
> However, as long as the sa account password is readily known by developers
> (and whomever else may know it), you really have no security for your
> database. (And if the application is hacked, the hackers will have admin
> privileges in the database.)
> There may be other regulatory reasons that you need to tighten up the
> security, i.e., HIPPA, SarBox, etc. The usage of the sa account will most
> likely be flagged as a security audit failure.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Stu" <stu@.blah.com> wrote in message
> news:uSCBYwc1GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Inline...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <stu@.blah.com> wrote in message
news:uZXUeon1GHA.3908@.TK2MSFTNGP05.phx.gbl...
> Many thanks for your advice to date Arnie. Could i just run some more
> questions past the group.
> After reading your reply, i'm thinking that yes in time the sa account
> will be withdrawn from use.
> I'm just trying to get my head around a plan of attack on how it will take
> place.
> The first step i want to take is to create a group for suitable DBA's to
> administer the server.
> I'm guessing that i'm also going to have to create a DBA Admin user, which
> will effectively take the place of the SA account, which will not as you
> mention be a member of the sysadmin group / role.
>
A new DBA Admin User login would only be useful for SQL Login purposes.
You're moving away from SQL Logins.
It may be useful to create a domain group that contains those appointed to
be the SQL Administrators. You can then assign that domain group to the
[sysadmin] role in the server. Anyone that is a SQL Administrator must b
e in
the sysadmin role. (It has the same 'power' as [sa], but the security yo
u
are moving toward is that a user must have their own domain account/password
in order to get into the server, and then if they are in the sysadmin role
they are allowed administrator priviledges. If you cannot create (or have
created) a domain group, then you could add each prospective administrator's
domain/username to the sysadmin role.
Also, if appropriate you can add users to the db_owner role in a database.
That role allows complete control of a database, create tables, etc., BUT
does not allow similar control for other databases on the same server. This
can be good for developers when a database is in development, and then
removed from developers when the database goes into production.

> Is the switch over just as easy as selecting the windows authentication
> radio button?
> if so, if the changeover is made, if things go wrong, will the role back
> be to retick mixed mode or would the initial changeover be not possible to
> reverse?
Yes, you can check the button for Windows Authentication, and if things
break, click on SQL Authentication to return it back to mixed mode. -It will
take a few seconds as the server has to stop/restart.

> also what changes will need to be done to the individual databases, just
> changing the connection strings?
>
Each application will have to have it's connection strings changed to allow
for the new security model. BUT first, you will have created a database role
for the application users, and added the domain groups/accounts to that
role.
It will also be necessary to go through each database and provide
appropriate permissions to that role for Tables, Views, Stored Procedures,
Functions.
Different applications that have their own databases 'should' each have a
distinct database role, so that a user in permissions in one database cannot
inadvertently access data in another database.

> once again, thanks for any further advice.
No problem.
For a good introduction into SQL Server security, see if you can find a copy
of Morris Lewis' book, SQL Server Security Distilled, ISBN 1-9043-47-07-X
http://www.abebooks.com/servlet/Boo...isbn=190434707X

>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23LVeCAd1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>

Changing port numbers

On an active/active cluster what is the proper way of changing the port # on
the named instance after an install is complete and in use.
Use the Server Network Utility to assign a new port number. The utility is
not cluster-aware so make the changes on all nodes in the cluster
individually. SQL server requires a service stop-start to change port
numbers.
Geoff N, Hiten
Microsoft SQL Server MVP
"zach_john" <zach_john@.discussions.microsoft.com> wrote in message
news:01F5E155-2002-4C8A-8549-80DA732C7B7F@.microsoft.com...
> On an active/active cluster what is the proper way of changing the port #
> on
> the named instance after an install is complete and in use.
|||Thanks Geoff - just wanted to hear that confirmation.
Cheers,
Zach
"Geoff N. Hiten" wrote:

> Use the Server Network Utility to assign a new port number. The utility is
> not cluster-aware so make the changes on all nodes in the cluster
> individually. SQL server requires a service stop-start to change port
> numbers.
> Geoff N, Hiten
> Microsoft SQL Server MVP
> "zach_john" <zach_john@.discussions.microsoft.com> wrote in message
> news:01F5E155-2002-4C8A-8549-80DA732C7B7F@.microsoft.com...
>
>
|||It's always easier to learn from someone else's mistakes.
GNH
"zach_john" <zach_john@.discussions.microsoft.com> wrote in message
news:9C40F75E-2E21-4026-9A34-CF4857CF78DD@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff - just wanted to hear that confirmation.
> Cheers,
> Zach
> "Geoff N. Hiten" wrote:

Changing Instance name

Does anyone know how to change the instance name on an installed server short of reinstall?

Scenario

We are about to deploy 20 2K5 servers. We can not deploy them remotely. We will send install disks into the field and use local network support tech to do the work. Currently, the servers are running 2K. We will be running a side-by-side upgrade to insure minimal down time.

Since this is a side-by-side upgrade, a second instance name is required. If our techs typo the required name our client apps will not be able to connect.

We can run a script remotely if we need to but can it be done?

Did you try Upgrade Advisor? Why Upgrade is not an option for you. Upgrading 20 servers 1 at a time is painful but not as painful as what you are trying to do.

Thanks

Raja

|||And the direct reply to your first question is that while we provide a way to deal with server renames, we don't allow instance renames. :(

Changing From Windows To Mixed Authentication

Hi All

I am trying to switch from Windows to Mixed mode authentication on SQL
Server 2000. I am right-clicking the server instance in Enterprise
Manager and selecting the correct option from the Security tab.

Unfortunately, the change I make does not persist when I click OK. I
get no error message, but when I re-open the server properties, the
option has returned to being Windows authentication.

Any ideas?

Thanks,

Kulgan.Did you restart the service ?

HTH, Jens Suessmeyer.|||Hi

I stopped and started the service using Enterprise Manager (i.e. right
click the server in Enterprise Manager and select Stop from the menu,
the start again using Start from the same menu).

Do I need to stop and start using some other method?

Thanks,

Kulgan.|||--------------------
http://support.microsoft.com/defaul...b;EN-US;q285097
--------------------
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
--------------------
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip
-URL--------------------|||Fantastic! Worked a treat!

Kulgan.

Changing From Default Installation

Hello,

Our security center just scanned my database server and has asked that I change the database server from the default instance name and change the default port used. When installed MS SQL Server 2000 I used the default installation settings. I have installed SP4 and this is all running on a fully patched WIN 2003 Server machine. Is this possible without un-installing and then re-installing?

Thanks for any and all help.

Ben

Renaming the instance is not possible. For installing a named instance, you will have to run the installer of SQL Server and install a named instance from scratch (and patch the instance afterwards with the appropiate service packs) The port can be changed using the SQL Server Configuration Manager:

Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration manager > SQL Server Network configuration > Protocols (Select the instance to configure) > TCP/IP / IPAdresses > Condigure the port for the appropiate IP Addresses

restart the service...done.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for the help!

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I get a new year. For instance if I run it this year I'll get 2004.txt for my file name and next year I'll get 2005.txt for my file name. Is this possible? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDate desc"' , no_outpu
t
You can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt for
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I
get a new year. For instance if I run it this year I'll get 2004.txt for my
file name and next year I'll get 2005.txt for my file name. Is this possib
le? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set noco
unt on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orde
rs O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by
OrderDate desc"' , no_outpu
tYou can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.c
md) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate fro
m Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.
CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o
'
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd)
+
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt fo
r
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

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

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/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&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/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&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.
>
>

Changing data source programatically does not transfer data

I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.

The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!

Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?

I think this may be the wrong approach. You can parameterize the package at execution time with the connection string of the source and destination. This is better, and easier, than modifying the package structure.

-Jamie

|||

That is exactly what I am doing Jamie. I am changing the Connection string property of the source connection. Still doesn't work. Check below the code snippet:

// Load the package

string sLPFPackage = @."C:\projects\CCS_DTS\CCS_DTS\LPFMigration.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package oLPFPackage = app.LoadPackage(sLPFPackage, null);

oLPFPackage.UpdateObjects = true;

// Modify the source and target data locations

sDataSource = "Data Source=" + sLPFDataFolder + ";Provider=VFPOLEDB.1;Collating Sequence=MACHINE;";

ConnectionManager oDataConn = oLPFPackage.Connections["SourceConnectionOLEDB"];

DtsProperty prDataSource = oDataConn.Properties["ConnectionString"];

prDataSource.SetValue(oDataConn, sDataSource);

// Execute the package

oLPFPackage.Execute();

Any ideas?

|||

Actually that is exactly what I am suggesting you do NOT do. The package can be parameterized from the command-line when executed via dtexec.exe without actually changing the package itself. I don't know much about executing package from an application but I have no doubt that you will be able to do the same.

Explore the /SET option of dtexec.exe for more information: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

-Jamie

|||Thanks jamie. Let me check it out. I will let you know if it helped.