Solved

Enabling TCP/IP/ protocol in sql server configuration manager using CMD

Posted on 2014-10-27
28
456 Views
Last Modified: 2014-12-05
Hello there,,

How can I  ENABLE TCP/IP protocol in sql server configuration manager using COMMAND PROMPT ?. I am using SQL server 2008.

cheers
Zolf
0
Comment
Question by:zolf
  • 16
  • 10
28 Comments
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40407782
Hi,

you can do it by powershell.

try this link http://msdn.microsoft.com/en-us/library/ms191294.aspx
0
 

Author Comment

by:zolf
ID: 40407785
this is not working for me. i get below error.....cannot i use simple cmd.

PS C:\Users\zaj> Import-Module "sqlps"
Import-Module : The specified module 'sqlps' was not loaded because no valid module file was found in any module direct
ory.
At line:1 char:14
+ Import-Module <<<<  "sqlps"
    + CategoryInfo          : ResourceUnavailable: (sqlps:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

I tried something like this in my cmd prompt. But it is not enabling the TCP when i check in sql server configuration manager

C:\Users\zaj>WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10 PATH ClientNetwork
Protocol WHERE ProtocolName='Tcp' CALL SetEnable
Executing (\\ZAJ-VAIO\root\Microsoft\SqlServer\ComputerManagement10:ClientNetworkProtocol.Proto
colName="tcp")->SetEnable()
Method execution successful.
Out Parameters:
instance of __PARAMETERS
{
        ReturnValue = 0;
};

Open in new window

0
 
LVL 6

Assisted Solution

by:Mandeep Singh
Mandeep Singh earned 475 total points
ID: 40407830
0
 

Author Comment

by:zolf
ID: 40407850
thanks for your help...but i get this error. I am copying the script as mentioned in that link

C:\>WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10 PATH ServerNetworkProtocol
Where ProtocolName='Tcp' CALL SetEnable
ERROR:
Description = Invalid query

Open in new window

0
 

Author Comment

by:zolf
ID: 40407886
it seems it is those quotes surrounding TCP which waS GIVING THAT ERROR. By the way something is going on here and that is I disable my tcp from the sql server configuration manager and then using the cmd i am testing to see if it enables it but it seems it is not enabling. what is wrong or what am i missing or doing wrong.
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40407910
The query works fine perfectly fine on my system, may be it's a error on your sql server. Do you installed Sql Server on your system or just client.
0
 

Author Comment

by:zolf
ID: 40407917
i have installed server 2008..weird!! via sql server configuration manager it works but not via cmd script
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40407928
it's a default instance or named instance.

if it is a named instance then use it like this:

WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10\instance_name
0
 

Author Comment

by:zolf
ID: 40407938
no its default instance
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40407943
Remove 10 after ComputerManagement10 from this and then give a try.
0
 

Author Comment

by:zolf
ID: 40408045
i get this error when i remove that 10

C:\>WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement PATH ServerNetworkProtocol Wh
ere ProtocolName='Tcp' CALL SetEnable
ERROR:
Description = Invalid namespace
0
 

Author Comment

by:zolf
ID: 40408057
here is a screen shot to make it more clear to my problem

z
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40408075
After you run this script in command prompt, just do a refresh on SQL Server Configuration manager.

or Close SQL Server configuration manager and then open it.

because it does not refresh by itself.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:zolf
ID: 40408077
the same issue i have with enable. that is i disable the tcp/ip from the configuration manager and then via the cmd i run the script to enable, it runs the script but when i check the manager it is not disabled
0
 

Author Comment

by:zolf
ID: 40408079
After you run this script in command prompt, just do a refresh on SQL Server Configuration manager.

I tried to refresh and also close and start the mssql instance but no luck
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40408081
i do not know what happening on your end but as seen in your screenshot query works fine.

i am working on same query and works perfectly fine with me.
0
 

Author Comment

by:zolf
ID: 40408085
yes the query is running but it is not applying those changes to the sql server. could it be that this script is doing this action on another sql server...as far as i know i just have one sql server instace which is called MSSQLSERVER
0
 

Author Comment

by:zolf
ID: 40408088
can we explicitly tell the script to get this instance of mssql and apply the changes...to be sure
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40408092
this script is doing changes on default instance by default that is MSSQLSERVER
0
 

Author Comment

by:zolf
ID: 40408097
i dont know why it is not working for me via the CMD script method
0
 
LVL 6

Accepted Solution

by:
Mandeep Singh earned 475 total points
ID: 40408101
In your screenshot i have seen you are using CLIENTNETWORKPROTOCOL.

You have to use

WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10 PATH ServerNetworkProtocol Where ProtocolName='Tcp' CALL SetDisable
0
 

Author Comment

by:zolf
ID: 40408105
OK, using this script also it i snot working.do you get the same output when you run the ServerNetworkProtocol. the reason is when i run the client i get the script output different compared to the server version.as you can see below the output when i run using the ServerNetworkProtocol

C:\>WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10 PATH ServerNetworkProtocol
Where ProtocolName='Tcp' CALL SetDisable
Executing (\\ZAJ-VAIO\root\Microsoft\SqlServer\ComputerManagement10:ServerNetworkProtocol.Insta
nceName="MSSQLSERVER",ProtocolName="Tcp")->SetDisable()
Method execution successful.

Open in new window

0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40408109
yes i got same result on execution, and my services are disabled and enabled by this script.
0
 

Author Comment

by:zolf
ID: 40408114
dont know what I am missing...frustrating...anyway thanks for your help!!
0
 

Author Comment

by:zolf
ID: 40408116
i will wait for a day if i dont get any solution i will except your answer
0
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 25 total points
ID: 40417253
every time you enable or disable a protocol you have to RESTART the sql server instance

sqlcmd -S tcp:localhost,1433
0
 

Author Comment

by:zolf
ID: 40423287
I did that,but still the changes are not reflected.weird
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now