We help IT Professionals succeed at work.

I restored a SQL database on a new 2016 server (SQL standard 2016).  Database seems to run however something seems wrong.

I restored a SQL database on a new 2016 server (SQL standard 2016).  Database seems to run however something seems wrong.  The database is for a CRM program called "office tools".  I have attached screen shots.

I believe I should see the SQL instance in Sql Server Configuration Manager however it does not appear.
SQLa.JPG
SQLb.JPG
SQLc.JPG
Comment
Watch Question

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

I don't find anything wrong there. Please give some more details what's wrong. You are successfully connected to the SQL Server instance, you can see restored database. Nothing unusual there. If you're worried by these XTP messages, please don't. There is a connect item registered for this by Robert L. Davis :
https://connect.microsoft.com/SQLServer/feedback/details/2876739/excessive-non-usable-messages-in-sql-log-when-running-checkdb-in-sql-server-2016

Open in new window

Author

Commented:
Hi Daniel_PL.  Thanks for the info.  My concern is when I look in the existing (old server) I can see items in the network config that the Office Tools FAQ points out.  I do not see these items in the new SQL server.

Depending on your network settings, you many have to add an exception to the firewall on your sever for the Dynamic Port associated to your SQL installation. The SQL dynamic port can be located in: Start> Programs> Microsoft SQL Server 2012> Configuration Tools> SQL Server Configuration Manager.

Expand “SQL Server Network Configuration (32bit)” and select “Protocols for NORTH40SYSTEMS”. Double click TCP/IP,  on the IP Addresses tab scroll all the way to the bottom and the TCP Dynamic Port is displayed in there. If you need assistance making an exception for this port in your fire wall contact our support team at (661) 794-2220 or support@officetools.com.

Office Tools server install link
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
New SQL Server uses default static TCP 1433 port, because it is a default instance (instance name is MSSQLSERVER). From Office Tools server, or wherever you're running your app, test whether port is open. Either by using telnet <srver address> 1433 or by using port query tool (provided by Microsoft).
https://www.microsoft.com/en-us/download/details.aspx?id=24009

Open in new window


The installation guide you provided is pointing new instance of SQL Server Express. As far as I understand you have your own licensed SQL Server Standard edition with restored database. You should config your software for this server. I'm not sure about this app modules but it seems database is being choosen in Workstation component:
https://www.officetools.com/knowledgebase/workstation-installation/

Open in new window


Regards,
Daniel
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I also don't see anything wrong. You have only a single SQL Server instance installed in that machine (PDC01), right?

Author

Commented:
Thanks for the comments.  The server name is "PDC01".  I used the port query tool from a client PC and 1433 is listening.  My concern is the clients can't connect to the office tools database.  I am attaching a screenshot.  The Office Tools client requires the following path "PDC01\NORTH40SYSTEMS".
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
You need to install your SQL Server as named instance, or just reconfigure your Office Tools client to point it to PDC01 SQL Server.

Author

Commented:
Here is the screen shot
SQL-port-screen-shot.JPG
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Cut \NORTH40SYSTEMS from server name, leave PDC01.

Author

Commented:
I tried that and same results.  AttachSql-screen-shot.jpged is the screenshot of Office tools instructions.

Author

Commented:
Is there a way I can see / browse the SQL instances running on the server from a client PC?
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
You need to enter just PDC01 into server name, nothing else.

Author

Commented:
Thanks, I tried that too and still the same results.  SQL-Capture.JPG
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Uncheck Use IP Address, test, if failed go to the SQL Server and check logs fo any errors.

Look here for example:
https://www.officetools.com/knowledgebase/workstation-installation/

Open in new window

Author

Commented:
This error is new.  I was not seeing this Friday when I restored the database.

Service: Instance Found
Error: System.Data.SqlClient.SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
   at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
   at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at AutoReminderEngine.Classes.AutoReminder.LoadIntervals()
   at AutoReminderEngine.Classes.AutoEngine.Start()
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1,State:0,Class:20
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I'm not sure how this software behaves but on the manual for Workstation component they don't have Show all instances checked. Therefore please uncheck it and try again.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You said you restored the database in a new server. How about the logins? You'll need to migrate or recreate the logins that have access to the database on the old server to the new one.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
They have this in the manual
The “Select Server” window will appear. Click the down arrow in the Server box and select “Your_Server_Name\NORTH40SYSTEMS” from the list.
I'm not sure but they might hardcoded instance name, which means your SQL Server needs to be named instance and it's name has to be NORTH40SYSTEMS. That's crazy, I son't see lot of software like this. If another attempt is not working I suggest to install SQL Server named instance with name: NORTH40SYSTEMS.

@Vitor,
It's not login issue, error states: (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
It sounds crazy but it really seems these gyus hardcoded instance name in the connection string...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can fool the system by adding an entry in the hosts file: C:\Windows\System32\drivers\etc\hosts

Edit the above file and enter the following line:
PDC01\NORTH40SYSTEMS 192.168.200.1

Then you can use PDC01\NORTH40SYSTEMS in the configuration screen.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I'm afraid you can't do something like that Vitor, did you try your solution? It won't work, the same is possible in native client alias, but you're only operating on server name level, not instance. What you want to achieve is to create hostname PDC01\NORTH40SYSTEMS. If creators of this software hardcoded instance name they'll probably build something like: PDC01\NORTH40SYSTEMS\NORTH40SYSTEMS if that entry will work in hosts, but it won't.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, I've tried this few times already. Usually during migrations and when we didn't know where the old SQL Server instance has been used so this guarantee us a smooth migration.
Other option is to change on the DNS in the AD.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If creators of this software hardcoded instance name they'll probably build something like: PDC01\NORTH40SYSTEMS\NORTH40SYSTEMS if that entry will work in hosts, but it won't.
If that's true it wouldn't work before with the old configuration when the author had the PDC01\NORTH40SYSTEMS configured.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Find attached file, maybe I'm missing something.
If that's true it wouldn't work before with the old configuration when the author had the PDC01\NORTH40SYSTEMS configured.
Not exactly, if it was default instance with SQL Server Express provided by OfficeTools installer then it should work fine. But it's not the case here. Author installed new SQL Server and from what I understood server name was adjusted to new hostname leaving instance name untouched.
hosts.jpg
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ah, it might need to have an alias created. I need to review that.
Thanks.

Author

Commented:
Here is 1 screen shot of the old sever.  The database was on SQL Express 2012Sql-old-server.JPG

Author

Commented:
Here is a second screen shot of the old serverSql-old-server2.JPG

Author

Commented:

Author

Commented:
sql.log is from the new server which is running SQL Standard 2016
DB Expert/Architect
Top Expert 2011
Commented:
As I stated before, you need to have named SQL Server instance. What I suggest is to install another instance on that server and remove default later.
Example installation is here, instead New_Instance you should type NORTH40SYSTEMS.
https://4sysops.com/archives/how-to-create-a-new-sql-server-2012-instance/

Open in new window


Regards,
Daniel

Author

Commented:
ok thanks! This helped a lot.  I created the new instance.  Now getting password error for the sa.  I didn't create the database and there is no record of the sa password.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

You need to enable mixed mode authtentication, restart SQL Server. After that enable sa account and set strong password for it.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode#SSMSProcedure

Open in new window


Next is restore or detach/attach database from default instance. Lastly retry Workstation component configfuration.

Author

Commented:
Thanks!