Solved

SQL Server 2008 remote connection issue via SSMS

Posted on 2012-04-11
12
240 Views
Last Modified: 2012-04-17
I am really pulling my hair out with SMSS and a new install of SQL Server 2008 R2 (WEB edition). I think I forgot to allow "Remote CDOnnections" on install. I know I can do this via Management Studio Express on the server, but we can not install it due to the stupid trial license bug that there have been thousands of complaints about.

Anyways, what I would like to do is use SMSS on my local dev machine to connect to my sql server but alas it will not connect;

On the server I have opened up all required ports and I have enabled TCP/IP connections using the external Ip address, but still I cannot connect to the server from my remote machine! SO, I am guessing this is the 'allow remote connections' problem?

Could it be anythign else?

Is there anyways to enable 'remote connections' via a command prompt for sqlserver 2008 r2?
0
Comment
Question by:swgdesign
  • 5
  • 4
  • 3
12 Comments
 
LVL 15

Expert Comment

by:deepakChauhan
Comment Utility
what error is showing when you connect it.
0
 
LVL 15

Expert Comment

by:deepakChauhan
Comment Utility
How to all remote connection from cmd

1.type cmd in the RUN utility

2.sqlcmd -Sservername\InstanceName -E if you are domaingroup user in sql sever

otherwise

sqlcmd -Sservername\InstanceName -UUsername -Ppassword

sp_configure 'show advanced options ,1
go
reconfigure
go
sp_configure
go
sp_configure 'remote admin connections', 1
go
reconfigure
go
0
 

Author Comment

by:swgdesign
Comment Utility
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

Thing is, I am not using Named Pipes, but an IP. So I can only assume it is being caused by "allow remote connections" not being enabled. I have setup SQL servers 10s of times and got them remotely configured but this one due to the issue with a trial license bug stopping us from installing SMSS on the server I cannot check!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>due to the issue with a trial license bug stopping us from installing SMSS on the server I cannot check!<<
Perhaps you can tell us where you read about this.  What "trial license"?  Are you referring to SQL Server Evaluation Edition?  If so there is no problem with that Edition, it is exactly the same as the Enterprise Edition.
0
 

Author Comment

by:swgdesign
Comment Utility
Noooooo. What it is this;

We had a trial version of enterprise while we were waiting for budget sign off for SQL Server install on the server. We got this but we're limited to WEB edition of 2008R2 (does not come with Managemetn tools). Anyways we installed WEB edition side by side to eliminate downtime and problems during migration.

Install went fine and so we installed SMSExpress to manage the WEB edition but when we uninstalled the Enterprise trial, for some reason our server still thiks that we are in trial mode even though we have a valid serial number etc.

Apparently, it is a bug between trial server versions and trying to get SMSE installed. I have tried numerous things written on the web but non have helped solve the issue.

In answer to your command syntax I cannot get it to work, it just hangs.

My server is called s15446692 and I think my instace is called SQLSERVER. Username is imc2011 and password is baxu84rs. Can you alter the code to include these as I think there might be spelling mistakes in the 1st line?
0
 
LVL 15

Accepted Solution

by:
deepakChauhan earned 500 total points
Comment Utility
c:\ sqlcmd  -Ss15446692\SQLSERVER -Uimc2011 -Pbaxu84rs and press enter

1> sp_configure 'show advanced options ,1
2> go
3> reconfigure
4> go
5> sp_configure
6> go
7> sp_configure 'remote admin connections', 1
8> go
9> reconfigure
10>go
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
We had a trial version of enterprise while we were waiting for budget sign off for SQL Server install on the server.
I have never heard of this.  The Evaluation Edition supports the full functionality of SQL Server Enterprise Edition.  So I have no idea why you had to the "WEB edition of 2008R2" and why you later had to uninstall the "Enterprise trial", rather than just a very simple Edition Upgrade.


Apparently, it is a bug between trial server versions and trying to get SMSE installed
Again, I have never heard of this.

But it looks like deepakChauhan is answering your question...
0
 

Author Comment

by:swgdesign
Comment Utility
We could not do a version upgrade from Enterprise trial to web edition as they both come with totally different aspects and would not upgrade or install due to an issue with an uknown sql analysis server that we could not find the name of.

Anyways that's irrelevant now.

Can I use sqlcmd with 127.0.0.1 instead of the named pipe as it just hangs otherwise?
0
 

Author Comment

by:swgdesign
Comment Utility
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Right, I thought you were referring to that.  But that is an SSMS problem and has nothing to do with SQL Server.  In other words if you successfully upgraded (or even re-installed) from the Evaluation Edition, then you are done with SQL Server.

If on the other hand you have installed the Evaluation Edition on a workstation as a client, than yes you will have to apply that fix to solve an SSMS (client software) problem.  Again nothing to do with SQL Server installation.

I am not sure where that leaves us and certainly none of this has anything to do with enabling remote access.

Can I use sqlcmd with 127.0.0.1 instead of the named pipe as it just hangs otherwise?
You can use whatever you like to connect to SQL Server.
0
 

Author Comment

by:swgdesign
Comment Utility
Well, I can tell you that this hapenned on a windows 2008 server between an Enterprise 2008 r2 trial and upgrading to Web r2 licensed.

Anyways, this is neither here nor there as this is now fixed. All inbound firewall connections have been created on our server according to the Microsoft spec. Could this be a local network issue blocking traffic out?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I have really no idea.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

10 Experts available now in Live!

Get 1:1 Help Now