🎉 🎉. RAPIDMINER 9.8 IS OUT!!! 🎉 🎉

RapidMiner 9.8 continues to innovate in data science collaboration, connectivity and governance

CLICK HERE TO DOWNLOAD

"Connecting to SQL-Server using Windows Authentication"

spitfire_chspitfire_ch Member Posts: 38  Guru
edited May 2019 in Help
Hi again

I stumbled over the next problem which I can't figure out on my own. When I try to connect to MS SQL Server 2005, I choose DatabaseExampleSource, Microsoft SQL Server (JTDS), Data Loading Wizard. I enter Server- and Databsae name. Then, I am prompted to enter user and password. However, we are using Windows Authentication, and hence I don't have a username or password. I can try to enter nothing, which won't work, or I can try to enter my windows login data, which won't work neither.

What would be the proper way to connect to SQL Server, when it's using Windows Authentication?

Thank you very much for your help!

Greetings,
Hanspeter
Tagged:

Answers

  • landland RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 2,531   Unicorn
    Hi,
    unfortunately Microsofts JDBC driver doesn't support Windows Authentification:
    The Microsoft SQL Server 2000 driver for JDBC does not support connecting by using Windows NT authentication. You must set the authentication mode of your SQL Server to Mixed mode, which permits both Windows Authentication and SQL Server Authentication.
    http://support.microsoft.com/kb/313100/en-us
    So I think you will have to change the server's setting to mixed mode...

    Greetings,
      Sebastian
  • spitfire_chspitfire_ch Member Posts: 38  Guru
    Thanx for your reply. I can't just change the connection mode of our company ;) But fortunately, the admin was able to open a "backdoor" to SQL-Server through which RapidMiner can connet :) (he basically installed a new user in the security settings of SQL-Server)
  • keithkeith Member Posts: 157  Guru
    The link regarding Microsoft's JDBC driver was pointing to outdated information applicable to SQL Server 2000.

    I am able to connect using Windows Authentication using Microsoft's SQL Server 2005 JDBC driver (not JTDS).

    Use a URL of the form:  jdbc:sqlserver://MYSERVER;integratedSecurity=true

    You can specify any non-empty value for username and password and it should work (it doesn't have to be a real username/password, it just needs some dummy value so that RM doesn't complain or prompt for it):

    <operator name="DatabaseExampleSource" class="DatabaseExampleSource">
            <parameter key="database_system" value="Microsoft SQL Server (Microsoft)"/>
            <parameter key="database_url" value="jdbc:sqlserver://MYSERVER;integratedSecurity=true"/>
            <parameter key="username" value="not_a_real_username"/>
            <parameter key="password" value="nkZ10Qek8Qw="/>
            <parameter key="query" value="select * from mytable"/>
    </operator>
  • landland RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 2,531   Unicorn
    Hi Keith,
    thank you for this valuable information!

    Greetings,
      Sebastian
  • ratheesanratheesan Member Posts: 68  Maven
    Hi Keith,

    Where you mentioned the name of database such as master,northwind ...in this url "jdbc:sqlserver://MYSERVER;integratedSecurity=true"

    Thanks
    Ratheesan
  • keithkeith Member Posts: 157  Guru
    ratheesan wrote:

    Hi Keith,

    Where you mentioned the name of database such as master,northwind ...in this url "jdbc:sqlserver://MYSERVER;integratedSecurity=true"

    Thanks
    Ratheesan
    In my configuration, the user I'm connecting as has its default database pointing to where my tables are, so I didn't need to specify a database (I'll automatically be pointing to the default database for my user).  If you need to specify a database name in the connection URL, you do so with the databaseName= parameter  e.g.
    jdbc:sqlserver://MYSERVER;integratedSecurity=true;databaseName=mydatabase
    This is not RapidMiner-specific, but is defined in the documentation for your JDBC driver, such as in the link below for the Microsoft SQL 2008 JDBC 2.0 driver:

    http://msdn.microsoft.com/en-us/library/ms378428.aspx

    If you're using a different version or a different driver, the syntax may not be identical, but you'll have to check the documentation for your specific driver to be sure.

    Keith
  • ratheesanratheesan Member Posts: 68  Maven
    Hi Keith,
    Thanks for your help.Here I am using SQL Server 2005 with windows authentication.But when I am trying to connect the mentioned way I am getting the error message "Database error occurred: Invalid integratedSecurity property value:true ".Here I am attaching my process,can you help me.

    <operator name="Root" class="Process" expanded="yes">
        <operator name="DatabaseExampleSource" class="DatabaseExampleSource">
            <parameter key="database_system" value="Microsoft SQL Server (Microsoft)"/>
            <parameter key="database_url" value="jdbc:sqlserver://computer-647;integratedSecurity=true;databaseName=dummudb"/>
            <parameter key="username" value="sa"/>
            <parameter key="password" value="oyJzE6CCwSg="/>
            <parameter key="query" value="select * from amb"/>
        </operator>
    </operator>

    Thanks
    Ratheesan
  • keithkeith Member Posts: 157  Guru
    I'm not certain why you're getting the error.  A couple of things that might be affecting it are:

    1) Is your SQL Server install configured to user Windows authentication?
    2) Is sqljdbc_auth.dll in the Windows system directrory, or a directory listed in the PATH environment variable on the client?
    3) Are you connecting from a non-Windows client?

    Beyond those suggestions, you'll need to consult the Microsoft JDBC or SQL Server documentation.

  • el_chiefel_chief Member Posts: 63  Maven
    See my video on YouTube, for how to connect RapidMiner 5 to SQL Server, including

    1. using a named instance

    2. using Windows authentication

    http://www.youtube.com/user/VancouverData

    "RapidMiner 5 Tutorial - Video 11 - Integration With SQL Server"

    Thanks

    Neil
    keith wrote:

    I'm not certain why you're getting the error.  A couple of things that might be affecting it are:

    1) Is your SQL Server install configured to user Windows authentication?
    2) Is sqljdbc_auth.dll in the Windows system directrory, or a directory listed in the PATH environment variable on the client?
    3) Are you connecting from a non-Windows client?

    Beyond those suggestions, you'll need to consult the Microsoft JDBC or SQL Server documentation.


Sign In or Register to comment.