Unable to connect to SQLServer on server using TCP/IP and no encryption

wadiachmed

Hello,

I am learning to use Rapidminer Studio 10.1 and trying to connect to SQL Server database on a server over TCP/IP, SQL Server version 2019, have set the connection with correct information such as the database username and password, host with instancename, port, and database name , as well as adding parameter encrypt = false in advanced tab, but the result is unable to connect with output message: Login failed for user 'performance_dashboard_read'



I use the DBeaver too for querying the data and database objects without connection issue. This tool uses JDBC driver as well as Rapidminer.

Would you please advise what parameters are missing from my settings, parameters, or drivers?

Thank you in advanced.
Best regards,
Iswadi

jwpfau

Hi,

Can you check if SQL Server Authentication Mode is allowed for the database in Microsoft SQL Server Management Studio?

Greetings,
Jonas

wadiachmed

Hi @jwpfau

Thank you for your response.


The SQL Server Authentication Mode is allowed.
The database is a custom instance name.

jwpfau

Hi, 

can you share the ~/.RapidMiner/rapidminer-studio.log file?
The error message looks like it's still trying to use Windows authentication mode, which would require additional native libraries on the path.

If you have access to commercial support, it's probably easier to contact them, since i have little experience with mssql.

Greetings,
Jonas 

wadiachmed

Hi Jonas @jwpfau,

I will check and share it tonight, since the laptop is left at my home.
Now I am at workplace that using another computer. 

My license is an educational. 

Thank you.
Best regards 
Iswadi

wadiachmed

Hi @jwpfau,

Here is my rapidminer-studio log file.

I have no ide on which line that identifying the parameter or other library caused the failure to connect to SQL Server database.

Best regards,
Iswadi

jwpfau

Hi,

i only found this thread on the microsoft forum:
https://learn.microsoft.com/en-us/answers/questions/1030610/unable-to-connect-sql-express-db-instance-via-jdbc

Does the password contain backslashes?

As an alternative you could also try the jdts driver.

Greetings,
Jonas

wadiachmed

Hi Jonas,

The password doesn't contain a backslash.
It contains these symbols *#!.
I put as it it on password field in Connection Editor, without additional escape character.

Tried to add more backslash on instance name in Host field, still no succeed



Thanks

jwpfau

Hi,

this might be the issue .

Can you try to wrap your password in curly braces? i.e. {your_password}

The MSSQL jdbc driver has some weird rules about passwords, not sure why they not just escape them:

 a login or password must not contain the following characters: [] {}() , ; ? * ! @. 

Greetings,
Jonas

wadiachmed

Hi
I tried to add curly braces in password field
It still didn't connect.

Thanks Jonas.

I'll try to contact customer support too.

jwpfau

Hi,

another thing, are you sure that the sqlprod instance runs on port 1433? 

Otherwise you might want to use a custom jdbc connection url without the :1433 part

For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15

Greetings,
Jonas

wadiachmed

Hi Jonas,

Yes it is running port 1433, using default of SQL Server port.
I can connect to this database using other software, named DBeaver using jdbc driver too. The DBeaver doesn't need to set property encrypt=false to connect to the SQL Server

Regards,
Iswadi