Enable SQL Server Remote Connections via Script

Hi,
I am connecting to my SQL server 2005 database using a remote connection.
Can someone provide me with a SQL script which will enable connections
via TCP/IP to the database? (i.e. I'd like to do the step of going to
the configuration manager and enabling the TCP/IP protocol for the SQL
Server Instance using a SQL Script instead of doing through the UI).
Thanks in advance!
- ramaduHello Ramadu,
Thank you for posting in the MSDN newsgroup.
From your description, I understand that you're wondering how to configure
the SQL Server 2005 database instance's remote connectivity settings(the
allowed protocols) programmatically (through commandline or script file),
correct?
Based on my research, as for the remote connection setting, generally we
can configure it in the SQL Server 2005's "Surface Area Configuration" tool
which is a GUI component. However, it has a commandline version (SAC.EXE)
which can execute command based on a XML file, and the xml file can be
exported from an existing server and use the SAC.exe to
import the setting on another machine with SQL 2005.
#sac Utility
http://msdn2.microsoft.com/en-us/ms162800.aspx
And here is a web article which demonstrate on the most common
configuration tools in SQL SERVER 2005:
#SQL Server 2005 Management Tools
http://www.informit.com/guides/cont...seqNum=178&rl=1
Also, if you're familiar with WMI programming, you can have a look at the
new WMI provider interfaces provided in SQL SERVER 2005:
#WMI Provider for Configuration Management
http://msdn2.microsoft.com/en-us/library/ms180499.aspx
http://blogs.msdn.com/sql_protocols.../19/482840.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven,
I'm not looking for a command line tool. I am rather looking for a
Stored Procedure / SQL Script which I can execute using the Query
Analyzer to enable remote connections.
The reason for this is that we are in the process of migrating from SQL
Server 2000 to SQL Server 2005 and in our application we provide a SQL
Script which out clients execute for doing updates to their database. I
wanted to include the enabling of remote connections in the same.
Hope you understood what I am looking for.
- ramadu
:
> Hello Ramadu,
> Thank you for posting in the MSDN newsgroup.
> From your description, I understand that you're wondering how to configure
> the SQL Server 2005 database instance's remote connectivity settings(the
> allowed protocols) programmatically (through commandline or script file),
> correct?
> Based on my research, as for the remote connection setting, generally we
> can configure it in the SQL Server 2005's "Surface Area Configuration" too
l
> which is a GUI component. However, it has a commandline version (SAC.EXE)
> which can execute command based on a XML file, and the xml file can be
> exported from an existing server and use the SAC.exe to
> import the setting on another machine with SQL 2005.
> #sac Utility
> http://msdn2.microsoft.com/en-us/ms162800.aspx
> And here is a web article which demonstrate on the most common
> configuration tools in SQL SERVER 2005:
> #SQL Server 2005 Management Tools
> http://www.informit.com/guides/cont...seqNum=178&rl=1
>
> Also, if you're familiar with WMI programming, you can have a look at the
> new WMI provider interfaces provided in SQL SERVER 2005:
> #WMI Provider for Configuration Management
> http://msdn2.microsoft.com/en-us/library/ms180499.aspx
> http://blogs.msdn.com/sql_protocols.../19/482840.aspx
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Study the registry. this setting is stored in the registry, so look at it be
fore and after you
modify using the tool. Then you can use xp_instance_regwrite to do the modif
ication from within SQL
Server. This xp isn't documented so use at your own risk, and a method which
it really preferable is
to ship with a CLR proc that you wrote and that does the registry modificati
ons for you. Another
thought is to have the CLR proc use SMO to do this instead of hacking the re
gistry directly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ramadu" <tnr@.newsgroups.nospam> wrote in message news:%23NMu$7ujGHA.3304@.TK2MSFTNGP03.phx.
gbl...
> Hi Steven,
> I'm not looking for a command line tool. I am rather looking for a Stored
Procedure / SQL Script
> which I can execute using the Query Analyzer to enable remote connections.
> The reason for this is that we are in the process of migrating from SQL Se
rver 2000 to SQL Server
> 2005 and in our application we provide a SQL Script which out clients exec
ute for doing updates to
> their database. I wanted to include the enabling of remote connections in
the same.
> Hope you understood what I am looking for.
> - ramadu
> :|||Thanks for Tibor's informative input.
Hi Ramadu,
I'm sorry for the misunderstand, I originally think that you're wantting
some shell script(vbscript, jscript) code to run such task on commandline.
Actually, to enable remote connections in SQL Server 2005 it'll involve
multiple steps:
#How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/?id=914277
and not all of them are possible to do in pure T-SQL script.
1) Based on my research, as for the "Surface Area configuration" setting,
we need to use "SAC.exe" utility, and if we want to integrate this command
into T-SQL script, we can consider use the "xp_cmdshell" extender command
which can help execute external commandline utility(such as the SAC.exe).
You need to enable it through sp_configure in script since it is disabled
by default:
#xp_cmdshell
http://msdn.microsoft.com/library/e...jxo.asp?frame=t
rue
However, since SAC.exe will execute based on a xml file, it will still
require external resources(can not completely be done in T-SQL). BTW, do
you think it convenient that you put all such external resource file(such
as the xml import/export file) in a shared folder location so that it can
be conveniently referenced by commandline tool(through xp_cmdshell)?
2) As for enabling server protocol, there are serveral approaches, however,
most of them rely on some external programming interfaces, such as WMI or
.net framework SMO objects(new in SQL 2005). If none of them is possible
for your scenario, I'm afraid the only way is to manipulate the registry
directly (frankly speaking, this is not what I would prefer or recommend).
The server protocols setting is under the following registry path (specific
to each instance):
===========
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\@.Ena
bled
===========
and there is some internal system SP which can help manipulate registry
values. e.g.
the below command enable the "TCP" protocol for the certain instance:
=============
EXEC master..xp_regwrite @.rootkey='HKEY_LOCAL_MACHINE',
@.key='SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp', @.value_name='Enabled',
@.type='REG_DWORD', @.value=1
===============
Here is a web article introduce some other such undocumented system
extended sps:
http://www.sql-server-performance.c..._procedures.asp
3) If there is also windows firewall enabled on the server, here is a kb
article discussing on programmatically open firewall ports for sqlserver:
#How to use a script to programmatically open ports for SQL Server to use
on systems that are running Windows XP Service Pack 2
http://support.microsoft.com/kb/839980
Hope this also helps.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Thanks Steven! I will look into these.
- Sri
Steven Cheng[MSFT] wrote:
> Thanks for Tibor's informative input.
> Hi Ramadu,
> I'm sorry for the misunderstand, I originally think that you're wantting
> some shell script(vbscript, jscript) code to run such task on commandline.
> Actually, to enable remote connections in SQL Server 2005 it'll involve
> multiple steps:
> #How to configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/?id=914277
> and not all of them are possible to do in pure T-SQL script.
> 1) Based on my research, as for the "Surface Area configuration" setting,
> we need to use "SAC.exe" utility, and if we want to integrate this command
> into T-SQL script, we can consider use the "xp_cmdshell" extender command
> which can help execute external commandline utility(such as the SAC.exe).
> You need to enable it through sp_configure in script since it is disabled
> by default:
> #xp_cmdshell
> [url]http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp?frame=t[/ur
l]
> rue
> However, since SAC.exe will execute based on a xml file, it will still
> require external resources(can not completely be done in T-SQL). BTW, do
> you think it convenient that you put all such external resource file(such
> as the xml import/export file) in a shared folder location so that it can
> be conveniently referenced by commandline tool(through xp_cmdshell)?
>
> 2) As for enabling server protocol, there are serveral approaches, however
,
> most of them rely on some external programming interfaces, such as WMI or
> .net framework SMO objects(new in SQL 2005). If none of them is possible
> for your scenario, I'm afraid the only way is to manipulate the registry
> directly (frankly speaking, this is not what I would prefer or recommend).
> The server protocols setting is under the following registry path (specifi
c
> to each instance):
> ===========
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\@.Ena
bled
> ===========
> and there is some internal system SP which can help manipulate registry
> values. e.g.
> the below command enable the "TCP" protocol for the certain instance:
> =============
> EXEC master..xp_regwrite @.rootkey='HKEY_LOCAL_MACHINE',
> @.key='SOFTWARE\Microsoft\Microsoft SQL
> Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp', @.value_name='Enabled',
> @.type='REG_DWORD', @.value=1
> ===============
> Here is a web article introduce some other such undocumented system
> extended sps:
> http://www.sql-server-performance.c..._procedures.asp
>
> 3) If there is also windows firewall enabled on the server, here is a kb
> article discussing on programmatically open firewall ports for sqlserver:
> #How to use a script to programmatically open ports for SQL Server to use
> on systems that are running Windows XP Service Pack 2
> http://support.microsoft.com/kb/839980
> Hope this also helps.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)|||Thanks for your response Sri,
Hope that will help you resolve the problem. If you meet any further
problem, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)