February 17, 2009 at 7:23 am
Using SQL Server Management Studio, I cannot reliably connect to other instances of SQL Server 2005, even though I am using the "sa" login and password on both machines.
I have used the SQL Server Surface Area Configuration Tool on all machines to allow local and remote connections using both TCP/IP and Named Pipes. The SQL Server Browser Service is enabled.
There are 2 pairs of machines involved:
1. At home, I have installed the SQL Server 2005 Developer Edition on my Windows XP Home Edition based PC. The SQL Server 2005 Developer Edition is also installed on my wife's Windows Vista Home Edition based PC.
2. At work, I have installed the SQL Server 2005 Developer Edition on my Windows XP Professional Edition based PC. The SQL Server 2005 Standard Edition is installed on our Windows Server 2003 R2 based production server.
Almost all attempts to connect fail, but not always. I've done a lot of trial and error experimentation.
Occasionally, usually not, the SQL Server Browser Service recognizes and locates the other instance of the SQL Server. If Named Pipes are enabled, I can sometimes (rarely) connect to the other machine.
However, the User Interface in SQL Server Management Studio looks odd. The little green triangle that is normally seen on the SQL Server instance icon is missing.
I have spent a lot of time on this. I need desktop access to the production SQL Server in my work as a Data Center Administrator.
Can anyone offer me any advice as to what I need to do to make this work?
Thanking you in advance,
Lee C.
February 17, 2009 at 7:50 am
In SQL Server 2005 you need XP pro and Vista Ultimate in your home setup because SSRS is IIS dependent. I am not saying your installation is not complete but when you are using the developer edition it is best to use operating system that can run all of it.
And Win2003 needs to be in SP2 because it fixed many connection related bugs.
So change the operating systems make sure you have used SAC to enable all you need, in the desk tops enable both TCP/IP and Named Pipes.
Kind regards,
Gift Peddie
February 17, 2009 at 6:42 pm
Gift,
Thank you for your suggestions. I installed IIS and verified MS Server 2003 SP2 but to no avail.
I still cannot connect the servers even though both SQL Browsers can see the other SQL Server.
LC
February 17, 2009 at 7:06 pm
Hello,
My first impression is that you probably need client VPN, or Remote Desktop enabled to manage the server. Kindly look into it if you aren't familiar with the tool.
Anyway, are you connecting by name or IP? Are you specifying the instance name as well? The default port is 1433, you should be to successfully telnet to the 'production servers' IP on 1433 by command line
Start -> Run -> cmd
telnet 1433
Regards,
Craig
February 17, 2009 at 7:07 pm
I told you to do more than install IIS the two operating systems in your house are home operating systems. Both comes without IIS enough to install SSRS so how did you get them to connect to Win2003 on the network level? The reason I am not sure you can connect both to a server because when an operating system is connected to a network the browser service shows you all SQL Servers on the same network. The permission to connect is not relevant.
Kind regards,
Gift Peddie
February 17, 2009 at 7:20 pm
Craig,
I am using port 1433 on both instances, addressing them by name (they are the default instances on their respective machines), and attempting to use SSMS to connect. The machines can ping each other using their IP addresses.
Gift,
I've given up on our home O.S.'s.
I spent about 2.5 hours at work today attempting to get a Windows XP Professional machine and a Windows Server 2003 machine to connect. They can see each other when they browse, but they will not connect by allowing a "sa" to log on to them. I have meticulously set all the required settings including enabling the named pipes protocol, turned on IIS. Nothing has worked.
LC
February 17, 2009 at 7:30 pm
Then you may have installed SQL Server with Windows authentication and so the sa account may be disabled. So you need to enable both Windows and SQL authentication and see if you need to reenable the SA account.
Kind regards,
Gift Peddie
February 18, 2009 at 7:23 am
No, Gift, that is not the problem. The "sa" account is enabled and mixed authentication was selected during installation. It is still selected.
LC
February 18, 2009 at 7:31 am
Then there must be firewall or anti virus software run by your employer that is blocking access. If that is not the issue then re run setup and choose repair and make sure you choose all components.
Kind regards,
Gift Peddie
February 18, 2009 at 7:46 am
Gift,
There is no AV on the server.
An associate and I have shut down firewalls on my computer and the server to attempt to connect. This did not work, either.
When I installed S.S. 2005 on my workstation, I did select all components for installation.
Which essential components might be missing and/or disabled on the server? They all appear to be present though some (like full-text search and SSIS) are not used. I enabled all of them during my attempts to connect.
LC
February 18, 2009 at 8:01 am
I see SQL Server I don't even have permissions to access so check the configuration manager of both instances and see how the services are running and accounts used to install each.
Kind regards,
Gift Peddie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply