In this section you need to ensure you have a user that can has the necessary level of rights in SQL Server and that the DP Agent will be able to connect to SQL Server.
- Create user DP_USER and grant ‘sysadmin’ privilege:
create login DP_USER with password =‘MyPW’
use <primary database>
create user DP_USER for login DP_USER
EXEC sp_addsrvrolemember ‘DP_USER’, ‘sysadmin’
2. Enable remote DAC – usually port 1343:
Start menu -> Microsoft SQL Server 2012 -> SQL Server Management Studio Login with sa account or a user in sysadmin group. Execute:
sp_configure 'remote admin connections', 1
sp_configure 'remote admin connections'
3. Enable TCP/IP
Start menu -> SQL Server Configuration Manager -> SQL Server Network Configuration
Make sure the TCP/IP status is Enabled.
Right click on the TCP/IP to enable it if it’s not enabled by default.
If you configure your MssqlLogReaderAdapter Remote Source and when doing connection test get an error that you cannot connect:
SAP DBTech JDBC: : internal error: Cannot get remote source objects: Adapter validation failed. Failed to establish JDBC connection. Error: The TCP/IP connection to the host *******, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
In that case make sure that you can telnet both into port 1433 or whatever port SQL Server is configured on, and also the DAC port 1343. If you are not sure if your SQL Server uses this port for DAC you can do this test bellow:
Obtaining the Microsoft SQL Server DAC port
1) Find the Microsoft SQL Server ERROR.log file in Microsoft SQL Server instance directory
2) Open the ERRORLOG file and search for the string “Dedicated admin” to find an entry similar to this:
2016-12-03 00:56:58.11 Server Dedicated admin connection support was established for listening locally on port 1434.
2016-12-03 00:56:58.11 spid10s SQL Server is now ready for client connections. This is an informational message; no user action is required.
1434 is the DAC port in above example.
You can also use this Microsoft link to troubleshoot communicaion access to your MS SQL Server DB.