Solved

SQL Server 2008 remote connection issue via SSMS

Posted on 2012-04-11
12
241 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
ID: 37832362
what error is showing when you connect it.
0
 
LVL 15

Expert Comment

by:deepakChauhan
ID: 37832409
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
ID: 37832420
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
ID: 37832460
>>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
ID: 37832538
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
ID: 37832695
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37833640
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
ID: 37838117
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
ID: 37839241
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37840568
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
ID: 37841515
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
ID: 37842437
I have really no idea.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 52
powershell and sql server - alerting 7 77
What should be a storage size for SQL in day1, day2 and day 3 7 78
Retention Policy for Backups 1 24
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

939 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

7 Experts available now in Live!

Get 1:1 Help Now