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?
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?
Deepak Chauhan
what error is showing when you connect it.
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
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
ASKER
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!
>>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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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?
Anyways that's irrelevant now.
Can I use sqlcmd with 127.0.0.1 instead of the named pipe as it just hangs otherwise?
ASKER
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/20/sql-server-2008-evaluation-edition-expiration-woes.aspx
The issue I spoke about! Although this did not fix it for us!
The issue I spoke about! Although this did not fix it for us!
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.
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.
ASKER
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?
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?
I have really no idea.