1. Open “services.msc” in Windows command line and check if SQL server service is running. If not, SQL server service abnormal should be a possibility which cause BC manager can't find SQL server database instance.
2. Enter SQL server enterprise manager, choose the corresponding database instance and enter database list. Check if database is online, if not, we need to online database instance and try again.
3. But the problem still exist, so we need to collect BC manager log and analyze it. Open “services.msc” in Windows command line, find and stop "RdAgent". Then, find RD Agent installation directory(like: C:\AgentV1R5), collect the whole "log" directory.
4. Open "slqserverinfo.log", then we can find the detailed information when BC manager try to add the SQL server instance.
Here is the error message found in the log:
di 14-06-2 12:45:44 "OSQLEXE path is C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql."
di 14-06-2 12:45:44 "Get sqlserver database version[ Microsoft SQL Server 2012 - 11.0.5058.0 (X64) ] successful."
di 14-06-2 12:45:44 "Query instance MSSQLSERVER DbNum 5."
di 14-06-2 12:45:45 "Query db[master] of MSSQLSERVER instance status:ONLINE, RetStatus."
di 14-06-2 12:45:45 "Query Db infos:MSSQLSERVER;master;0;master;0;0;;."
di 14-06-2 12:45:46 "Query db[tempdb] of MSSQLSERVER instance status:ONLINE, RetStatus."
di 14-06-2 12:45:46 "Query Db infos:MSSQLSERVER;tempdb;0;tempdb;0;0;;."
di 14-06-2 12:45:47 "Get database model of MSSQLSERVER instance status not ONLINE, RetStatus."
di 14-06-2 12:45:47 "sqlinfo:Null."
di 14-06-2 12:45:47 "SQLSERVERDBINFO:1> 2> No permission to access database 'model'."
di 14-06-2 12:45:47 "SQLSERVERDBINFO: name "
di 14-06-2 12:45:47 "SQLSERVERDBINFO: db_size "
di 14-06-2 12:45:47 "SQLSERVERDBINFO: owner "
Please note the bold words. When the BC manager add SQL server hosts, it should inquiry the database path first, as the log "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql".
Second, it should inquiry database instance status. In this case, we can see two database instance "master" and "tempdb", they're both online.
But, later, when BC manager try to get database model, it returns "status not ONLINE" and "No permission to access database 'model'"
So, we can see the reason should be BC manager has no permission to access database.
5. The procedure to check the permission of BC manager in SQL server as below:
1). Open SQL server manager;
2). Right click the database which can’t add to BC manager;
3). Check the Permissions, whether guest account has the connect permission to the database;
4). If not, click “Search”, input “Guest”, then enable “Grant” option for connect operation, as below:
By referring the procedure, the we fixed 2 in 3 servers. Another one server still can't be added to BC manager.
6. Collect the BC manager log again and analyze the "sqlserverinfo.log".
Here is the error message in the log:
wo 22-06-2 14:48:57 "OSQLEXE path is C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql."
wo 22-06-2 14:48:57 "Do not connect the stand alone instance[MSSQLSERVER]."
"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql" -b -E -i"C:\Huawei\tmp\OsqlExit3.txt"
wo 22-06-2 14:48:58 "Get sqlserver database version failed."
wo 22-06-2 14:48:58 "Execute sqlserverinfo.bat end, exit 0."
This time we can see BC manager can't connect database install at the SQL path.
7. Next step, we need to check if the path is wrong. BC manager get the SQL server installation path by searching Regedit, so we need verify in Regedit.
1). Open “regedit”
2). Find: "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server", check directory, like below:
3). Check the path key in each directory like below:
8. In the case, the customer told us, there's two path key found in Regedit, "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\\osql." and "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\\osql." So, it represent that there must be two version of SQL Server in this server. The customer confirmed that there's SQL server 2014 and SQL Server 2008.
According to compatibility list, SQL Server 2014 is not supported by BC manager yet.