"Connecting to SQL-Server using Windows Authentication"

spitfire_ch

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

land

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_ch

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)

keith

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>

land

Hi Keith,
thank you for this valuable information!

Greetings,
  Sebastian

ratheesan

Hi Keith,

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

Thanks
Ratheesan

keith

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

ratheesan

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

keith

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_chief

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.