how to use linked servers in 2008 server

[bookouri]

I have successfully created some linked servers in management studio. I can select records from tables on those linked servers without any trouble. BUT, how can i grant select access to those linked tables to a specific user? I can't find any references and I can't find anywhere in management studio where I can grant my user permissions to select from those linked tables. Can anybody point me in the right direction to using linked server tables?

thanks

 

[Qik3Coder]

Look for topics on the "guest" account.
SQL logins are restricted to the server/database they are created on. When you use a windows account it just re-authenticates to the new server.


A lack of experience doesn't prevent you from doing a good job.

 

[bookouri]

Im only using sql server logons for this project, no windows logons involved.

I'm trying to select from tables in a linked server from visual studio. When I make the connection in visual studio, the only way i can find to get to the linked table is by creating a view that references the linked table. When I do this I can get the view to work like its supposed to. When I go to visual studio I can connect and use the view as long as I connect as SA. BUT, I have a user that I have granted select permissions to the view and when i try to connect as that user in visual studio I get an error "cannot initialize the data source object of OLEDB provider "OraOLEDB.Oracle" for linked server...etc.."

Im guessing I have some kind of permission problem but dont know where to look. Anybody have any idea what i have to do to give my user permission to run the view?

 

[bookouri]

I have managed to push enough buttons to finally get the permissions for my user to work. I removed my sql server user from the database link security and set the link up to logon with my oracle user credentials. Now my User can execute the query. But, a simple query using the database link will run for longer than 10 minutes just to return a few rows.

 

[Qik3Coder]

Are you doing a cross server join, that is mixing MS SQL and Oracle?

You may want to consider using an application to mash that data together.

Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[bookouri]

nope, nothing but a simple select c1,c2,c2,c4 from t1, no joins at all... just a "flat" table sitting on the oracle server that i need some columns out of for some drop down boxes and things like that..

but i can NOT get anything like usable performance.. i've used links from one oracle database to another to do such things, but i cant seem to make it workable so far with sqlserver

 

[Qik3Coder]

I'm gonna ask the dumb question. Have you done the select from the oracle server as a baseline for performance?

How static is the data? Have you entertained the idea of a job that runs X times a day refreshing the data in the MS SQL tables and then selecting from there?

Why can't you just connect directly to the Oracle tables from your app if you don't need to mix it with some MS SQL data?

Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[bookouri]

The data is pretty static, selects work fine on the oracle side. I could do some kind of table replication X times a day, but I'm finding lots of cases where I just need to populat a drop-down list or something like that and it would be "cleaner" and easier to maintain if it was always up to date and only in one place.

I can connect directly to oracle but Im finding in moving to .net and using Visual Studio that I can't develop a project on my local machine and publish it and it actually WORK when Im having to use oracle connections instead of sqlserver. With SQL Server connections I can just develop, test and publish and they work. I can not get oracle connections to cooperate that way. The only way I can get Visual Studio projects to work correctly with oracle is to install Visual Studio on my production Web Server and do my development right there in place.

 

[Qik3Coder]

The fog is lifted.
Are you implying that your dev database is Oracle, but you are connecting via MSSQL because you're having a problem connecting directly to it from the Visual Studio client?

It sounds like you are having a connection string and appropriate object type to use problem. There are special SQL classes in .Net meant for MSSQL, and more generic ODBC object for "the other databases"

You have to use the correct one. As well, if you are developing on your dev box and it works correctly, then you have a connection string problem. connectionstrings.com has a sample for just about every type of db that you should need.

I'd open a thread in the C# or VB2008 forum asking for sample connect to oracle. I don't have any, or I'd paste one for you.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30 yet."[/red]

 

[bookouri]

I spent some time at connectionstrings.com in the past, and I can make connections manually on either development environment or production. I was trying to use database links to "get around" the real problem of not being able to get Visual Studio to cooperate. The real problem is how to set up my development environment so that an oracle link that works on my development machine STILL works when i publish the project to the web server. I can work all day on my development machine and everything works fine and then i publish it to the web and my oracle connections don't work, or i can edit the site "in place" on the live machine and everything works fine. I just cant get my development environment and production coordinated somehow.. I guess the right thing to do is see if there's a visual studio forum and try to work it out there rather than "get around" the problem with database links.

 

[Qik3Coder]

It doesn't sound like a visual studio problem.

Do have have special Oracle dll's in your GAC, that you didn't propogate out to the production server?

Do you have an additional machine that you can use to validate your website install from? Normally you will get a pretty obvious error when it tries to connect and is missing some required component.


And the dumb question last:
Does the account that you have set up in the Production web.config for the Oracle database actually have access in Production?

Lodlaiden



[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]