Cannot connect to SQL Server 2014 using BODS 4.2 SP5

[former_member198401]

Hi All,

Need your help urgently!!

We are trying to connect to SQL Server 2014 Database by creating a Datastore of SQL Server 2014 Type. We are currently on DS 4.2 SP5. Job Server is on Unix.

When I am trying to connect I am receiving the below attached error.

Can anyone confirm if jtds-1.2.7.jar is used to connect to SQL Server 2014 due to its compliance or security part. I am trying to use a Windows AD Authentication to connect to SQL Server 2014. As per PAM BODS 4.2 SP5 supports SQL Server 2014.

Any hints will be appreciated and rewarded

Regards

Arun Sasi

[former_member187605 · Accepted solution]

You really confuse me. What are you trying to achieve?

When you set up a SQL Server based datastore with Windows authentication, indicate it as such in the Advanced section and do not specify user name and password:

At runtime, your jobs will use this connection info, too. But your job server is on Unix! Do you have both authentication mechanisms integrated?

[former_member198401 · Accepted solution]

Hi Dirk,

Just saw the Whats new in DS 4.2 and found that Windows Authentication is not supported in 4.2 on Unix platform.Please confirm if it is still applicable for DS 4.2 SP5 version

Windows authentication for the MS SQL Server datastore is not supported on the UNIX platform.

We have not installed SQL Server Native Client on DS client machine. So in the current scenario only SQL Server Authentication is supported, but as per the Security compliance we need to use Windows Authentication as it a secured database server. Once I get added in the Windows AD group I will try to use Windows Authentication option in Advanced settings of Datastore.

As I have understood I need to install SQL Server Native Client to access SQL Server 2014. Once we have done this we will install the necessary drivers for SQL Server 2014 on our new Unix Job Server which is on 4.2 SP5.

I hope I have clarified my requirement

Regards

Arun Sasi

[former_member187605 · Accepted solution]

Yes, that's what I meant, by definition there's no Windows authentication on a Unix box. That still applies.

You will need an exception for DS, define a specific service account with SQL Server user name and password. Windows authentication will never work in your environment.

[former_member198401 · Accepted solution]

Hi Dirk,

As windows authentication is not supported with Job Server on Linux, I created an JDBCAdapter type Adapter datastore and I was able to extract the SQL Server Tables. The only disadvantage of this adapter datastore is I cannot use the SQL Server 2014 tables as Target. I guess its a limitation.

We will create a SQL Server 2014 datastore with SQL Server user authentication when we get the account created on the SQL Server database. So until then we will use our Adapter datastore.

Regards

Arun Sasi

[former_member187605 · Accepted solution]

Yes, jdbc datastores are read-only. If you need read/write, you'll have to use odbc or native drivers.

[former_member224495]

Hi Arun,

If you are using windows account to connect the SQL DB you should be having full access to your windows account to particular DB

and as suggested by dirk please follow the steps while creating the DS when you use windows account to connect.

FYI....

Please let us know if it works fine after making the necessary changes 🙂

Thanks,

Sagar T

[Former Member]

Yes BODS 4.2 SP5 supports SQL Server 2014, because i am using it in the current project. It might have to do with some security related issues.

[former_member198401]

Hi Arun,

Do we need to install SQL Native client on DS 4.2 SP5 client machine for connectivity with SQL Server 2014.

Let me know if any drivers need to be installed.

Regards

Arun Sasi

[Former Member]

Hi Arun,

SQL Native client is another way of connecting the DB with DS. In my case the connectivity is through native client and windows authentication. You could try native client, though i don't see any reason why ODBC doesn't work.

Arun

[former_member254877]

Hi Arun -

I think, you need to have SQL Native client installed in your system to connect SQL server DB.

[former_member198401]

Hi Srini, Arun, Magesh,

Thanks for your inputs!!

I will try to install SQL Server Native Client. Can you tell me which version of SQL Server Native client I need to install.

I will be using Windows Authentication

Regards

Arun Sasi

[former_member254877]

Try installing SQL Server Native Client 11.0 which supports Sql server 2012 & 2014

[former_member198401]

Ok Srini!!

I will try installing SQL Server Native Client 11 and update

Regards

Arun Sasi

[mageshwaran_subramanian]

Arun,Are you able to connect to the database with SQL Server management studio or any other SQL editors (with the exact same information you provided to create the data store)?. I believe you are not using Windows authentication to connect to SQL server.I suspect the invalid login details causes the problem.

[Former Member]

Hello Arun,

Just a silly observation, you are trying to connect to a SQL Server 2014, but, in the picture, the database version is Microsoft SQL Server 2012.

Could you check if it is the reason?

[former_member198401]

Hi Nestor,

Nice captcha

I guess thats by mistake. I had tried with SQL Server 2014 and it seems that due to security compliance I am not able to connect to the database. I have requested to add my AD account in to the user group which has access to the required database.

The strange thing is that when I tried with the integration account it is still giving the error. Do I need to install any driver or deploy jar files like jtds-1.2.7.jar which supports SQL Server 2014 connectivity

Regards

Arun Sasi