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

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
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mandeep SinghDatabase AdministratorCommented:
Hi,

you can do it by powershell.

try this link http://msdn.microsoft.com/en-us/library/ms191294.aspx
0
zolfAuthor Commented:
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
Mandeep SinghDatabase AdministratorCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zolfAuthor Commented:
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
zolfAuthor Commented:
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
Mandeep SinghDatabase AdministratorCommented:
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
zolfAuthor Commented:
i have installed server 2008..weird!! via sql server configuration manager it works but not via cmd script
0
Mandeep SinghDatabase AdministratorCommented:
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
zolfAuthor Commented:
no its default instance
0
Mandeep SinghDatabase AdministratorCommented:
Remove 10 after ComputerManagement10 from this and then give a try.
0
zolfAuthor Commented:
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
zolfAuthor Commented:
here is a screen shot to make it more clear to my problem

z
0
Mandeep SinghDatabase AdministratorCommented:
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
zolfAuthor Commented:
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
zolfAuthor Commented:
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
Mandeep SinghDatabase AdministratorCommented:
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
zolfAuthor Commented:
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
zolfAuthor Commented:
can we explicitly tell the script to get this instance of mssql and apply the changes...to be sure
0
Mandeep SinghDatabase AdministratorCommented:
this script is doing changes on default instance by default that is MSSQLSERVER
0
zolfAuthor Commented:
i dont know why it is not working for me via the CMD script method
0
Mandeep SinghDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
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
Mandeep SinghDatabase AdministratorCommented:
yes i got same result on execution, and my services are disabled and enabled by this script.
0
zolfAuthor Commented:
dont know what I am missing...frustrating...anyway thanks for your help!!
0
zolfAuthor Commented:
i will wait for a day if i dont get any solution i will except your answer
0
David Johnson, CD, MVPOwnerCommented:
every time you enable or disable a protocol you have to RESTART the sql server instance

sqlcmd -S tcp:localhost,1433
0
zolfAuthor Commented:
I did that,but still the changes are not reflected.weird
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.