0

Background:

There is a custom java application specified to connect localhost database User-PC\MSSQL to work with. This machine need to be replaced with another new PC with better hardware.

Issue:

As the custom java application is created by someone long time ago, I cannot track back who is the developer and ask him to update the database path. I installed SQL Server 2005 on the new PC, renamed the machine name to User-PC then use sp_addserver and sp_dropserver to rename the SQL server name to MSSQL (Reference link for the sp_addserver and sp_dropserver tutorial that I followed). However, I still cannot the database by connecting to User-PC\MSSQL via SQL server management studio or HeidiSQL or the custom java application. I can still only connect the new PC database by localhost.

Improve this question
3
  • Set the same user and password for connect to the instance if you know that. In DB when you restore to new instance delete the user and apply the new user with same name. – Santiago Jun 30 '16 at 17:13
  • @Santiago I dont have the old sa account password… it uses Windows account authorization to login, I have export it to excel format then import it on the new PC – Bilo Jul 1 '16 at 5:00
  • Not an answer: Most people faced with a legacy application that is hard to port, will just convert the physical to virtual (P2V) and run the VM forever as virtual. – harrymc Jul 2 '16 at 6:40
1
+50

Sounds like you're almost there..

However, this is potentially one of those issues that could have multiple causes. So first and foremost you'll need to verify that SQL Server configuration manager has SQL Server configured to listen on the correct interfaces (the external one too if needed)

Due to firewalls, you may want to consider statically setting the listener port to ensure that the next time the server starts it doesn't potentially listen on different one and register itself with SQL Server browser with new port information (which will potentially be firewalled too =) ).

You'll need to also verify that SQL server browser service IS RUNNING and its listener ports ARE ALSO not firewalled or SERVER\Instance will not work within connection strings. The purpose of this service is to resolve named instances to their respective listening ports for incoming client connections that use server\instance within their connection strings (Kind of like a "DNS server" in a way but mapping named instances with their port numbers instead) since you can have multiple named instances running on one machine.

Anyhow, let me know if this helps . Thanks for posting.

Reference: SQL Server TCP and UDP Ports

Improve this answer
3
  • Thanks for your information, just one quick question as the machine is in data center and far away to get to there to test this thing :( If I am not planning to run two instance on localhost, I am just renaming the existing instance running on localhost (but with MACHINE_NAME\INSTANCE), does firewall port and SQL server browser service matter? – Bilo Jul 4 '16 at 2:07
  • You're welcome.. Note that anytime you reference a connection string as MACHINE_NAME\INSTANCE SQL server browser needs to be enabled and not firewalled. If the server is configured to be the default instance, The only thing required within your connection string is MACHINE_NAME and the \INSTANCE portion can be omitted (Just make sure the SQL Server is listening on port 1433) and likewise, browser service would not need to be enabled. Hope this helps. – NotAdmin Dave Jul 4 '16 at 2:17
  • The only remaining issue you'll experience after it works are potential issues related to authentication failures. Since you created a new server, you may need to recreate the logins again on the server since the existing database grant accounts likely are phantom due to them not having physical logins on the server. Once those accounts are recreated you can use the sp_change_login command to update the grants for those logins within the database to remap them with their newly created logins. Then authentication issues will disappear and everything and everyone will be happy. – NotAdmin Dave Jul 4 '16 at 2:28
0

What I would suggest if applicable is to change the order of things. On a brand new system without SQL installed:

  1. Change the name of the computer to User-PC
  2. Install SQL and during the installation it will ask for the instance name and you can specify it to be MSSQL

Check to see if you can connect. We do those setup commonly and I know this is a reliable method.

If that doesn't work, it may be a good idea to try to find out what the actual instance stored in sql is by running a query. The query will tell you what the database server name should be when you try to connect

SELECT CONVERT(sysname, SERVERPROPERTY('servername'))

This returns what you actually need to use. This may still be the old value in your case since server name is actually different. See this question.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.