Summary
Information about establishing SQL connection over TLS 1.2 using InstallShield
Synopsis
Including SQL scripts in a Basic MSI project is organized by connection. No script can run on a server until a connection has been established. InstallShield requires a SQL Server ODBC driver or OLE DB provider to use the SQL Scripting functionality.
Discussion
If you want to use the new capabilities that Microsoft SQL Server offers, and you want to employ TLS 1.2 (Transport Layer Security 1.2) you must configure InstallShield to use a driver that supports them. Otherwise, InstallShield SQL connection will not work.
The default ISSQLDBMetaData entry for Microsoft SQL Server is configured to use a SQL Server OLE DB provider. A change is required to use a different OLE DB provider and you have to change the value of the AdoDriverName column specific to that provider. Since connection strings for ODBC drivers are different from OLE DB providers, more settings may need to be changed in your InstallShield project. This article is intended to provide the manual settings required in the IDE when targeting connections through TLS 1.2.
This is a non-exhaustive list. Some of the various connection issues you may experience are described below. Each one may require any or all of the specific settings required for TLS found in the Additional Information section below:
- Provider cannot be found. It may not be properly installed.
- A network-related or instance-specific error has occurred while establishing a connection to SQL Server (-2146893007).
- ODBC Driver 11 for SQL Server invalid connection string (0).
- SQL Server Native Client 11.0 SSL Provider. The client and server cannot communicate because they do not possess a common algorithm.
- SQLState: ‘08001’ SQL Server Error 18 — See KB Article Installer is unable to create a database connection — working with TLS 1.2
After the expected TLS settings are established and enabled on the server and client in your environment manually and you have added a connection through the InstallShield IDE change the InstallShield project manually to allow connection.These changes are specific to allow the SQL connection to use ODBC Driver for SQL Server compliant with TLS 1.2.
Note: The table ISSQLDBMetaData is populated with data after a New SQL Connection is added in the IDE. Reference: SQL Scripts View
1) Modify the Property Manager specifying a new value. In this step navigate to Behavior and Logic > Property Manager. Locate the property IS_SQLSERVER_NETLIB_MS and change its value.
Property Manager
| IS_SQLSERVER_NETLIB_MS | Network=DBMSSOCN |
2) Using the Direct Editor change all of the data specified below in the ISSQLDBMetaData table.
ISSQLDBMetaData table
| AdoDriverName | {ODBC Driver 11 for SQL Server} |
| AdoCxnDriver | Driver= |
| AdoCxnDatabase | Database= |
| AdoCxnUserID | Uid= |
| AdoCxnPassword | Pwd= |
| AdoCxnWindowsSecurity | Trusted_Connection=Yes |
Related Documents
Reference:
Introducing TLS v1.2
Microsoft ODBC Driver for SQL Server
Note: The KB #3135244 from Microsoft is very relevant and should be an article used for reference with any matter involved with TLS 1.2.
TLS 1.2 support for Microsoft SQL Server
Additional Information
The following is a list of details implied to be established when working with TLS 1.2.
- SQL Server version and cumulative update/patched per Microsoft specification. Reference KB #3135244.
- .Net 4.5 or newer installed
- TLS/SSL settings established found here: TLS/SSL Settings
- Ensure SQL server service is running on server
- TLS 1.0 and TLS 1.1 disabled in the Registry
- Certification authority with the hash algorithm as SHA 256
- SQL Server Native Client drivers installed (see KB #3135244)
- ODBC Driver for SQL Server installed (see KB #3135244)
- Specific Operating System Updates with TLS 1.2 support established (see KB #3135244)
- SChannel key block — restrict protocol communication on servers to TLS 1.2 only in the Registry
- Ensure the SChannel data in the Registry under ..SCHANNELProtocolsTLS 1.2 includes both Client and Server (Image below)
Note: When working with or changing SChannel key block or any of the GPO (Group Policy) settings it is recommended both server and client be restarted.
- Remove From My Forums
-
Question
-
Error 27502. Could not cnnect to Microsoft SQL Server ‘servernameinstancename’
[DBNETLIB][ConnectionOpen(Connect()).]SQL server does not exist or access nedied. (17)
This message comes when trying to connect to an instance through installation of a software. Software is called Huldt&Lillevik and I am running the database-setup from the terminalserver.
Also tried to create a ODBC connection with Named Pipes and TCP/IP from the terminalserver, but I also there get the same error message.
Any suggestions what this can be?
Answers
-
Okey, I have now found the problem. When I created the instance it did not assign any ports because another instance had used 1433. Assigned a new port for the instance, opened it on our firewalls and then it worked.
-
Marked as answer by
Saturday, September 24, 2011 10:39 PM
-
Marked as answer by
- Remove From My Forums
-
Question
-
I’m attempting to install Canon’s software that will allow us to monitor their copies machines from a central location. I’m installing it on MS Server2005 «server A». We have another «server B» that has a handful of SQL Server 2005 databases, it is on that server in which I want to install the database. When going thru the install wizard on server A I tell it to put the database on server B and it requires a user account/password. We created an account called Canon.Service and gave it SA access on Server B. However I receive the following error:
Error 27502. Could not Connect to Microsoft SQL Server. Login failed for «Canon.Service». The user is not associated with a trusted SQL Server connection.
Any help would be greatly appreciated…
steve
Answers
-
From your explanation you created a SQL Server Login Account but the server isn´t configured for SQL Server authentication (Mixed authentication). If you need mixed authentication and cannot achieve the connection with a trusted (Windows) authentication, then you can change it through the SSMS > Right Click on the registered server to administer > Properties > Security > Mixed authentication. Make sure you restart the service for making the changes effective.
-Jens
Jens K. Suessmeyer http://blogs.msdn.com/Jenss
-
Proposed as answer by
Monday, May 18, 2009 8:02 PM
-
Marked as answer by
Jens K. Suessmeyer —Microsoft employee
Monday, May 25, 2009 6:33 AM
-
Proposed as answer by
Target Audience: IT Manager
Article #: 12346
Summary
This article explains the BACTRACK error message “Error 27502. Could not connect to Microsoft SQL Server…” We also detail how to fix the underlying problem that caused it to occur.
Explanation
Essentially, Error 27502 is a “server not found” issue.
The most frequent cause is the user mistyping the server, domain or computer name. Since BACKTRACK requires that the name is entered using a specific format, it is easy to enter the correct name, but use the wrong format.
Generally, the format required by BACKTRACK uses the same format used by Microsoft SQL Management Studio:
[DB Server or Domain Name][SQL Instance Name]
Quick Fixes
If the instance of SQL is set per recommendations, it should be visible by pressing the down arrow or Browse button. However, if it is not visible, possibly due to security restrictions, the user can type it in using the correct format shown above.
There are four different methods to identify the SQL instance.
Issue found in BACKTRACK v2015.
If you need further assistance, please contact us.
Dave Klement
Principal at Efficient Business Systems
Learn how to resolve the message «Error 27502. Could not connect to SQL Server» in SendSuite Tracking.
Products affected: SendSuite® Tracking
Issue
When installing or upgrading to SendSuite Tracking version 14.2 or higher, the message «Error 27502. Could not connect to SQL Server [server name]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configure…» appears.
Cause
The SendSuite Tracking 14.2 release contained a change of the data connection provider from SQLOLEDB to SQL Native Client 11 (SQLNCLI11). If the server does not have the SQLNCLI11 driver installed, the error will appear when the installer attempts to connect to the SQL Server instance.
Resolution
- On the application server, download and install the SQL Native Client bundle from Microsoft.
- Restart the server, if required.
- Attempt the installation again.
Test Criteria
- Open the ODBC administrator:
- In the Windows Search field, type ODBC.
- Click on ODBC Data Sources. Choose 32-bit or 64-bit based on your operating system’s architecture.
- In the ODBC Data Source Administrator window, click on the Drivers tab.
- Confirm that the SQL Server Native Client 11.0 is installed and that all of the values are as follows:
- Name: SQL Server Native Client 11.0
- Version: 2011.110.x.x. or greater
- Company: Microsoft Corporation
- File: SQLNCLI11.DLL
- Date: 1/6/2018 or newer
UPDATED: August 14, 2021
Summary
When Installing the SEI Licnese Server we get the error, Error 27502. Could not connect to Microsoft SQL Server ‘SERVERNAMEX3’. [DBNETLIB]ConnectionOpen (Connect().SQL Server does not exist or access denied. (I7)
Resolution #1
Open port in Windows firewall for TCP Access
- Open Administrative Tools, Windows Firewall with Advanced Security console
- In the left pane of the console, select Inbound Rules
- Right-click select New Rule
- Select Port and click Next
- In the Protocol and Ports dialog box, select TCP.
- Select Specific Port Numbers and then type the port numbers: 1433, 1434
- Select Next until get to “Specify the name and Description of this Rule” screen
- Enter name for Inbound Rule (for instance SQL – TCP)
- Select Finish
Resolution #2
If the above does not resolve the issue follow the steps below to open access to SQL Server through Windows Dynamic Ports
Open Administrative Tools, Windows Firewall with Advanced Security console
- In the left pane of the console, select Inbound Rules
- Right-click select New Rule
- Select Program and click Next
- In the Program dialog box, select ‘This program path.’ Select Browse and navigate to Instance of SQL Server that you want to access through the firewall, and then click Open. (Example: C:Program FilesMicrosoft SQL ServerMSSQL11Instance_NameMSSQLBinnSqlserv,exe)
- Select Next until get to “Specify the name and Description of this Rule” screen
- Enter name for Inbound Rule (for instance SQL – Dynamic)
- Select Finish
Background:As part of our SixBit installation, we download and install a copy of SQL Server 2014 Express. SQL Server 2014 Express is a program from Microsoft. Our installation is really no different than the manual process of downloading SQL Server 2014 Express yourself and installing, we just do it for you automatically.
Symptom:
Sometimes SQL Server will not install on a computer for various reasons (i.e. it requires a reboot, a required component is missing, etc.). When that happens, the SixBit install can’t perform a proper installation and therefore won’t work properly. A 27502 error is usually the result.
Possible Causes:
There are a few things you should check right away before continuing.
- make sure your hard drive is not compressed. SQL Server will not install on a compressed drive
- make sure your computer name is NOT the same as your login name. SQL Server installs will fail if they are the same. If they are the same, change your computer name under your System settings on the Control Panel.
- SQL Server can be very picky about requiring installation on a newly rebooted computer. If you have problems installing, you should reboot and try the installation again immediately after rebooting.
- during the SixBit installation, when you are asked to specify the SQL Server location, click the database dropdown and choose «(local)SIXBITDBSERVER». Then choose «SQL Server Authentication» and enter a userid of «sa» and a password of «S1xb1tR0x».
Solution:
If the Possible Solutions did not identify the problem, then proceed to the following steps.
1. Follow the steps in How to Tell if the SixBit Agent and Services are Running to make sure the SixBit Agent and SQL Server (SIXBITDBSERVER) services are running. If you restarted the services, try the SixBit installation again. If you still receive the error or if you didn’t find the services listed continue to the next step.
2. At this point, perhaps the simplest fix is to manually install SQL Server 2014 following the steps at Installing SQL Server 2014 for SixBit. After manually installing SQL Server, try the SixBit installation again. If you still receive the error continue to the next step.
If the SQL Server installation failed, continue to Step 5.
3. Check your SQL Server Configuration Settings. If you still receive the error continue to the next step.
4. Check your remote connection settings in SQL Server Management Studio. If you still receive the error continue to the next step.
5. SQL Server cannot be installed on a compressed drive. To check to see if your drive is compressed, go to My Computer and right-click the drive you are trying to install SixBit onto. Select Properties. Near the bottom, make sure the «Compress this drive to save disk space» is unchecked. If it is checked, then you will have to uncheck it. This may involve having to remove enough data on your drive to allow the remaining data to be uncompressed.
If this did not solve the problem, proceed to the following steps to identify the reason SQL Server will not install:
1. SQL Server will log its attempted installation in a folder called C:Program FilesMicrosoft SQL Server150Setup BootstrapLog. There are several files in this folder that can help us understand why SQL Server won’t load. Go to this folder now.
2. Open the file Summary.txt in the Log folder. This is a summary log of your latest SQL Server Installation attempt. It may include clues as to why SQL Server wouldn’t install. Se the List of Summary.txt messages below for more information.
3. In the folder C:Program FilesMicrosoft SQL Server150Setup BootstrapLog you will find one or more folders. Each will be named with a date of an attempted installation. Open the folder for the latest installation attempt and look at the file named SystemConfigurationCheck_Report.htm. That file will provide the results of the system check to see if your system is acceptable for SQL Server 2014.
4. If you haven’t found the problem yet, look at the files that start with: Detail or Summary. Check it against the list of errors at the bottom of this article to see if it identifies the cause.
5. If you still haven’t been able to figure out the issue, please submit a support ticket with a copy of the LogSet_YourPCName_2010MMDD_HHMMSS. This is a zip file containing all the logs.
List of Summary.txt messages and their cause:
The following errors may appear in the Summary.txt file.
Attributes do not match. Present attributes (Directory, Compressed), included attributes (0), excluded attributes (Archive, Compressed, Encrypted). — This error is caused by trying to install SixBit on a drive that is compressed. SQL Server requires an uncompressed drive.
» is not a valid login or you do not have permission. — This error is caused when your computer name and user account name are the same. Change the computer name.
Error 27506 — the server could not load DCOM (7404) — This error occurs when the installation program is not allowed to create the initial database. It is caused when a firewall on your computer is preventing this action. Known security programs that cause this are CA Security Suite (California Associates) and nVidia Hardware firewalls. Try deactivating the security software, then perform the installation and reactivate when completed.
An error occurred during the installation of assembly Microsoft.VC80.CRT,version=»8.0.50727.4053″,type=»win32″,publicKeyToken=’1fc8b3b9a1e18e3b»,processorarchitecture=»x86″. Please refer to Help and Support for more information. HRESULT:0x800736FD — This error can be solved by running the VC++ Upgrade at http://www.microsoft.com/downloads/details.aspx?familyid=766a6af7-ec73-40ff-b072-9112bab119c2&displaylang=en#filelist