topic Re: sql server 2008 connections in Data Quality

sql server 2008 connections

_AnonymousUser — Sat, 16 Nov 2024 13:51:50 GMT

I have some issues viewing objects in a sql server 2008 database
I can connect to the database instance in open profiler (which uses windows authentication) using the Microsoft MSSQL Server JDBC Driver, however I can only see a few schemas, those schemas owned by dbo, guest, sys. However in this particular database the tables I want to perform data profilling on are in schemas not owned by dbo, and I can't see any of these in the DQ Repository window.
When connecting to a sql server 2005 database, using the same driver and windows authentication I can see all the schemas and objects within them but in this DB all schemas are owned by dbo.
In toad/SQL server management studio which connect using windows authentication too - I can see all the schemas/tables including those not owned by dbo. Is there any way around this, is it the JDBC drivers or open profiler, do i need to connect a different way, or do I need to get the DBA to change the access/permissions for my username?
Also while on the subject is there any way round the fact that I can't actually run SQL statements within open profiler, I am guessing its because of using windows authentication to connect to the DB?
Thanks for any help.

Re: sql server 2008 connections

Anonymous — Wed, 22 Jul 2009 17:19:01 GMT

hi,
maybe you can try with this driver :
http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en
Change the Sql server drivers in your prefrences and see.
I didn't test it but maybe.
Regards

Re: sql server 2008 connections

_AnonymousUser — Thu, 23 Jul 2009 02:50:40 GMT

Thanks for the suggestion.
I downloaded that driver, and tried to use it via the generic JDBC connection properties, however i keep getting the error message that it doesn't support integrated security. After googling the error, i found reference to the fact that the software had to be aware of the dll that is downloaded with the JDBC driver: sqljdbc_auth.dll in order to support window authentication.
I have tried putting this dll in the same folder as the driver, putting it in quite a few of the open profiler directories and putting it in the system32 directories, but I can't get it to connect using this driver. I know the driver works as I have managed to connect to the DB with Data Cleaner software. In that case I just put the dll in the same directory as the driver. Interestingly tho I am having the same issues in Data Cleaner not seeing the tables, I can only see tables that are in the DBO schema.

any other ideas would be great tho

Re: sql server 2008 connections

Anonymous — Thu, 23 Jul 2009 10:06:53 GMT

I found this but maybe it is the same as you found.
http://blogs.msdn.com/jdbcteam/archive/2007/06/18/com-microsoft-sqlserver-jdbc-sqlserverexception-this-driver-is-not-configured-for-integrated-authentication.aspx
I keep looking.

Re: sql server 2008 connections

_AnonymousUser — Fri, 24 Jul 2009 01:35:29 GMT

Right we've found a way round this
I couldn't find any way to get open profiler to use the Microsoft jdbc driver with the required dll for windows authentication so I am still using the JTDS driver.
However after our DBA here played around with roles, owners, users etc we have finally got a working solution.
We created a user 'BaseSchema' with the same name as the schema 'BaseSchema', we also made this user the schema owner (this may not be required). This now means this user 'BaseSchema; appears under the database in the DB connections tree structure, and now shows all the objects associated with the schema 'BaseSchema'
Is this a bug, when connecting to a SQL Server DB, it would seem that rather than displaying 'users' associated with a DB, it should really show 'Schemas' ?

However, follow up on my 2nd issue:
Any ideas on how I can actually run SQL statements within open profiler when connected to a SQL Server DB via windows authentication? For example in the analysis results window, if i click 'view rows' I get the following error message:
"Cannot conect to database alias
Cannot connect testConnection/root. Check your url
Login failed for user 'root'
why is it trying to connect with root rather than the windows authentication?

Re: sql server 2008 connections

Anonymous — Tue, 28 Jul 2009 17:14:46 GMT

hi !
Talend open profiler doesn't work with windows authentication on SQL server 2008 ( this feature will be available soon )
you should create one user in your database ( right-clic on connexions ) and une this user to create DBmetadata under profiler
i give more details for how to create a new user under SQL SERVER 2008 if you don't succeed
Good lucks

Re: sql server 2008 connections

_AnonymousUser — Mon, 14 Sep 2009 15:33:25 GMT

Hi.
I have a problem when i try to establish a connection to a microsoft sql server 2008 database on Talend Open Studio 3.1.3.
I have made a few screenshots that you can see on this link
http://dl.getdropbox.com/u/1227529/misc/ProblemConnectingSQLDB.pdf
to illustrate my problem. Can anybody help me please?

Re: sql server 2008 connections

Sebastiao_Qlik — Mon, 14 Sep 2009 15:50:20 GMT

Hi Thelma,
please, create a new topic in the https://community.talend.com/t5/Archive/Sybase-Connection/td-p/56802.
This forum is concerned with Talend Open Profiler and you may get a faster reply if you post in the appropriate forum.

Re: sql server 2008 connections

_AnonymousUser — Mon, 14 Sep 2009 23:50:03 GMT

Tk u =). I will post there.

Re: sql server 2008 connections

Anonymous — Sat, 24 Oct 2009 14:20:14 GMT

see attached document ( pdf ) on this bug :http://talendforge.org/bugs/view.php?id=9606#bugnotes