[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

SQL Server 2008 remote connection issue via SSMS

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
swgdesign
Asked:
swgdesign
  • 5
  • 4
  • 3
1 Solution
 
Deepak ChauhanSQL Server DBACommented:
what error is showing when you connect it.
0
 
Deepak ChauhanSQL Server DBACommented:
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
 
swgdesignAuthor Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Anthony PerkinsCommented:
>>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
 
swgdesignAuthor Commented:
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
 
Deepak ChauhanSQL Server DBACommented:
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
 
Anthony PerkinsCommented:
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
 
swgdesignAuthor Commented:
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
 
swgdesignAuthor Commented:
0
 
Anthony PerkinsCommented:
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
 
swgdesignAuthor Commented:
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
 
Anthony PerkinsCommented:
I have really no idea.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now