How to connect different SQL servers without using linked server connection to read data?
Question
Thursday, July 11, 2013 4:01 AM
Hi,
We're trying to read data from another SQL Server through our SQL server but we're not allowed to do that using Linked Servers. Please suggest an alternative here as the DBA says Linked Servers are not as per managed service standards. This is quite urgent please.
All replies (24)
Thursday, July 11, 2013 4:06 AM
Yes. OPENDATASOURCE AND OPENROWSET is there but they have their own disadvantages. Linked server is recommended.
http://technet.microsoft.com/en-us/library/ms190312.aspx
http://msdn.microsoft.com/en-us/library/ms179856.aspx
Srinivasan
Thursday, July 11, 2013 4:29 AM
Both these options also have been blocked. Is it possible some kind of web-service as middleware to pass data from one server to another?
Thursday, July 11, 2013 4:53 AM
You can connect other SQL servers from your computer in Database name on connectivity page type name of SQL server you want to connect and in Login select SQL server authentication give user name password and you will be able to connect if connectivity is there....
You can register other servers on Your sql server..see this link, if ur not able to connect contact ur DBA he will guide you.
Hope this helps
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, July 11, 2013 4:55 AM
Have no idea about middleware. I had a powershell script which pulls data from oracle and put them into sql server. But that was for on-demand and very light so I was ok with it. You will need to consider many options.
Srinivasan
Thursday, July 11, 2013 5:55 AM | 1 vote
Hmm, if you need the READ permission , why not asking the admin to create a linked server for your requirement with limited credentials. I mean create a read only login on the linked server and create a linked server with that credentials.
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Thursday, July 11, 2013 6:06 AM
You can always have a linked server with restricted access as Uri suggested. However, you can use powershell/.NET/SSIS for this.
Cheers!!! SqlFrenzy
Thursday, July 11, 2013 6:30 AM
They have denied creating a linked server saying it is not a good practise. I've found a link that uses a web service and can be called through a stored procedure. Just trying that out if it works but WCF is giving me a headache.
http://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur
Thursday, July 11, 2013 6:40 AM
I don't think you need this. you can do this in a more easy way. What's your exact requirement?
Cheers!!! SqlFrenzy
Thursday, July 11, 2013 7:32 AM
I just need to read data from another SQL Server in our DB to make some insertions through a stored procedure which is present in our database. I could have done that using Linked servers but OpenRowset/OpenDataSource and linked servers all are not allowed.
Thursday, July 11, 2013 8:06 AM
I am not sure but I think it can be done through CLR stored procedure.
If not, then you can do it through the front end application instead of the procedure.
Assuming you are using .NET
- read data into datatable from another sql server
- modify and insert data into your database.
Thanks, Ahmad Osama www.ahmadosama.net http://www.sqlservergeeks.com/people/AhmadOsama
Thursday, July 11, 2013 8:11 AM
Another method is to ask them to create a SSIS package to populate the table and then you can SELECT on that table locally.
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Thursday, July 11, 2013 11:37 AM
But how does CLR stored proc help in this case? If I create one, it will be created in their database in the other server which would again require connecting to that server through my stored procedure in some way. I hope I am not getting confused here.
Thursday, July 11, 2013 11:45 AM
You could use OPENROWSET, which'll require the connection info, username & password...
While I understand that the client believes that having an always-on connection to their data is risky, that's why you lock down the account. OPENROWSET means including the connection info in plain text
Regards, Thiru.
Thursday, July 11, 2013 12:09 PM
Check with your team for SSIS implementation. SSIS is looking ideal candidate for you.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thursday, July 11, 2013 1:42 PM
They have denied creating a linked server saying it is not a good practise.
Then ask them what is acceptable and if it's out of this world crazy what they expect, escalate it through your manager.
Denying you the ability to do anything and not giving you possible solution isn't very helpful.
-Sean
Sean Gallardy | Blog | Twitter
Friday, July 12, 2013 5:35 AM
My last resort is to try and call web service through a stored procedure.
Friday, July 12, 2013 6:03 AM
But how does CLR stored proc help in this case? If I create one, it will be created in their database in the other server which would again require connecting to that server through my stored procedure in some way. I hope I am not getting confused here.
You can create a CLR procedure in your sql server specifying the connection string with in CLR of the other sql server to read data from. The other alternative, which is using .Net can be a suitable solution.
Your call, however using webservice as an alternate to linked server is doing so much work for a simple thing. if not CLR/SSIS/powershell , you can look into my other solution of using .NET/Or any front language you are using to easily do this task.
Thanks, Ahmad Osama www.ahmadosama.net http://www.sqlservergeeks.com/people/AhmadOsama
Monday, July 15, 2013 8:38 AM
Do you guys think SSIS package can help? I just need to read data from a table to fetch values from only 3 columns from a different server in the stored procedure from my server.
Monday, July 15, 2013 9:17 AM
SSIS is a good option, but again that depends on the requirement. I would not suggest SSIS if you are thinking an on fly retrieval of data. I mean your proc calls SSIS package for an online data. But if your business can afford a delay, then probably SSIS can fetch the data and saves at local, there by your on fly proc can hit the local table and get the data.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Monday, July 15, 2013 10:26 AM
I don't know much about SSIS package. Can you please tell me a feature of SSIS which can help me achieve this? Like data flow task is used for ETL. Would this help me in acomplishing this task of reading 3 columns from a table by passing a parameter?
Friday, July 19, 2013 9:53 AM
yes, you can use dataflow task to copy data. However I would still insist on using .Net to do this as it will be a real time solution.
Thanks, Ahmad Osama www.ahmadosama.net http://www.sqlservergeeks.com/people/AhmadOsama
Friday, July 19, 2013 10:21 AM
Hi,
The thing is, we are providing an integration point to some team from back-end through our stored procedure. The other team is going to read the output of that stored procedure. Using SSIS, we have to think of a way to replicate the data from some other server to our own.
Friday, July 19, 2013 10:40 AM | 1 vote
SELECT *
FROM OPENROWSET ('SQLOLEDB','Server=sqlinstancename;UID=;PWD=',
'SELECT @@servername as server_name')
AS tbl
In above you no need to create linked server I am using above for fetching the info of the all server in our network. To use above you need to create a sql credential user.
Tuesday, May 30, 2017 10:18 AM
Limited credentials can cause performance issues:
Refer: https://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/