One Star

sql server 2008 connections

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.

  • Data Quality
9 REPLIES
Employee

Re: sql server 2008 connections

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

Re: sql server 2008 connections

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
Employee

Re: sql server 2008 connections

One Star

Re: sql server 2008 connections

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?
One Star

Re: sql server 2008 connections

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
One Star

Re: sql server 2008 connections

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?
Employee

Re: sql server 2008 connections

Hi Thelma,
please, create a new topic in the Talend Open Studio forum.
This forum is concerned with Talend Open Profiler and you may get a faster reply if you post in the appropriate forum.
One Star

Re: sql server 2008 connections

Tk u =). I will post there.
One Star

Re: sql server 2008 connections

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