The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here

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

wadiachmedwadiachmed Member Posts: 7 Learner II
edited June 5 in Help
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
Tagged:

Best Answer

  • jwpfaujwpfau Employee-RapidMiner, Member Posts: 303 RM Engineering
    Solution Accepted
    Hi,

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

    Greetings,
    Jonas

Answers

  • wadiachmedwadiachmed Member Posts: 7 Learner II
    Hi @jwpfau

    Thank you for your response.


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


  • jwpfaujwpfau Employee-RapidMiner, Member Posts: 303 RM Engineering
    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 


  • wadiachmedwadiachmed Member Posts: 7 Learner II
    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
  • wadiachmedwadiachmed Member Posts: 7 Learner II
    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

  • jwpfaujwpfau Employee-RapidMiner, Member Posts: 303 RM Engineering
    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
  • wadiachmedwadiachmed Member Posts: 7 Learner II
    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
  • jwpfaujwpfau Employee-RapidMiner, Member Posts: 303 RM Engineering
    edited June 10
    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
  • wadiachmedwadiachmed Member Posts: 7 Learner II
    edited June 10
    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.
  • jwpfaujwpfau Employee-RapidMiner, Member Posts: 303 RM Engineering
    edited June 11
    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

  • wadiachmedwadiachmed Member Posts: 7 Learner II
    edited June 11
    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
Sign In or Register to comment.