April 2, 2008 at 7:51 am
Any tips, links anyone has to share on the subject of SQL CLR and webservices, especially when it comes to security?
Our devs want to deploy SQLCLR functionality that utilizes web services, and I know nothing about the subject! :w00t:
It seems I've opened pandora's box by enabling SQLCLR on our systems...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 2, 2008 at 8:09 am
Heh... you certainly did.
I'm mostly a data troll, so I don't really know... but my gut tells me that SQL Server probably shouldn't be calling a Web Service... maybe the other way around. I'm sure someone will jump in with more than a simple "gut" feeling.
You said they want to call the Web Service using a CLR... what does that Web Service do and why do they want to do it from SQL Server?
--Jeff Moden
April 2, 2008 at 8:12 am
Jeff Moden (4/2/2008)
Heh... you certainly did.I'm mostly a data troll, so I don't really know... but my gut tells me that SQL Server probably shouldn't be calling a Web Service... maybe the other way around. I'm sure someone will jump in with more than a simple "gut" feeling.
You said they want to call the Web Service using a CLR... what does that Web Service do and why do they want to do it from SQL Server?
Very valid point. 🙂
We're having a meeting next week, and this is certainly going to be one of my questions, ie. why not do this from a middle tier instead?
But... assuming they have valid reasons, how can this be done safely and what does it involve?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 2, 2008 at 8:15 am
I'm not sure this is worse than a linked server (or opendatasource), but I'm with Jeff and I'm nervous about allowing this.
If you enable it, use your judgement, be careful of delays (meaning keep this out of transactions) and secure it well with limitations between specific machines.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 2, 2008 at 8:24 am
Hi Marios,
It will really depend on "why" the development team want to use the integrated CLR feature of SQL Server 2005 for web service. I am an advocate of using new features within the product, but only once the understanding is crystal clear. Assuming your development team have a valid reason and it makes good architectual sense, then you will need to ensure a basic level of security is implemented and development standards agreed.
Without much more detail, I can't really comment, but here is a good reference point to help you:
http://www.devx.com/codemag/Article/31193/0/page/2
Thank you,
Phillip Cox
MCITP -DBAdmin|MCTS - SQL Server 2005|MCP - DBAdmin SQL Server 2000
April 2, 2008 at 11:41 am
Marios Philippopoulos (4/2/2008)
But... assuming they have valid reasons, how can this be done safely and what does it involve?
That much I do know... It's going to be just like any other code that might use, say, xp_CmdShell or sp_MakeWebTask... a code review of the source code will need to be carefully done to ensure there's no chance of injection attacks, misuse of the code to attack either the WebServer, the SQL Server, or the associated Windows Server (or any other place it can gain access to). Then, you have to decide on who/what will be able to run it and make sure the security around that is pretty much bullet proof. Dunno if an "SA" proxy login would come into play here like it does for xp_CmdShell or sp_MakeWebTask, but it's an option that could come into play to ensure security and still allow full functionality of the non-generic code.
--Jeff Moden
April 2, 2008 at 1:16 pm
Here is what I know so far:
Developers want to deploy the CLR sproc to a single UTILITY SQL instance and allow linked server connections to the sproc from other instances. The code will locate local web services - within the intranet - and execute their methods. The URLs will be restricted to a database we control internally, and end users will not be able to enter a URL other than that predetermined set.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 2, 2008 at 3:20 pm
Maybe I'm not thinking of this right, but - why? The purpose of the web services is to allow for exposing specific things, so why would you introduce extra hops in the process?
I could even see having a web service that tells of OTHER web services, but there's no sense introducing the extra hops. Meaning - the web interface the user is connecting to should plenty competent to go talk (directly) to a remote web service; having it go talk to a SQL Server, to run CLR, so that IT can go talk to a remote web service, well - sounds like a lot of extra work, no?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 4:08 pm
- for starter : avoid clr as much as you can !
- Consume webservices ? Remind your devs that webservices may be down/offline/overloaded/.... So not available
How would that relate to the consumed data ?
If that does not relate to the consumed data, don't use clr, but consume the webservice directly from your apps.
- Keep in mind this will slow down your sqlserver if your ws is generating a huge volume of data. It may even bring down your sql-instance !!
- If using a clr-function, it may even be directly joined in a simple sql-query. How are you going to tune that ? You'll probably only get a flame call/male from some hotshot complaining his report/app doesn't respond fast enough and you can be sure of it that it is your server to blame ! (whos *** will be kicked ?)
- When consuming webservices, SAFE is nolonger in your dictionary !
- Here is a nice article and converstion regarding consuming a WS to perform geocoding. http://qa.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/geocodingwithsqlserver2005/2373/
Certainly worth the reading !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2008 at 6:11 pm
Is there a performance benefit to calling the web service within SQL Server, pass it the required parameters retrieved from the database, retrieve the results and update the local data, all within the same SQL process, instead of:
(1) ADO.NET to the database to retrieve the web service parameters.
(2) Call the webservice with the obtained parameters from .NET code and retrieve the results
(3) ADO.NET the results back to the database to update the local data
I'm inclined to think the first route would be much faster...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 3, 2008 at 12:04 am
Well, in case of large data-transfer I especialy would dislike using the clr.
Your sqlserver instance is at risk !
Check my reply on Jeff's article "Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5" in the CLR test scenario http://qa.sqlservercentral.com/Forums/Topic449802-203-4.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 9:52 am
ALZDBA (4/3/2008)
Well, in case of large data-transfer I especialy would dislike using the clr.Your sqlserver instance is at risk !
Check my reply on Jeff's article "Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5" in the CLR test scenario http://qa.sqlservercentral.com/Forums/Topic449802-203-4.aspx
By the way - the relevant reply seems to be on page 17....
(that was a dirty trick there, Johan!)
Antonio - you know better what you're trying to do than we do. We tend to use Web Services to retrieve data, not necessarily update other tables, so I guess I based my thought around that kind of usage. But still - you will find that unless there's a good reason the two SQL server instances shouldn't talk directly, performance-wise they will do better with a direct link (openquery, linked server, etc...). Of course - that may be exactly why it's being done.
The double/triple/quadruple hop scenario always tends to get messy, and less secure, so just tread carefully.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 10:18 am
I read the reply on page 17: http://qa.sqlservercentral.com/Forums/Topic449802-203-17.aspx
Scarry stuff...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 3, 2008 at 11:47 pm
Matt Miller (4/3/2008)
...By the way - the relevant reply seems to be on page 17....
(that was a dirty trick there, Johan!)
...
Dynamic paging ... :Whistling:
In my SSC-forum-profile I've set "Posts Per Page" to 50 :w00t:
Hence the faulty page ref...:blush:
The next time I should just refer to the forums page 1 .....
This way the click-reader will get the full message history as well...;)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 4, 2008 at 6:44 am
ALZDBA (4/3/2008)
Matt Miller (4/3/2008)
...By the way - the relevant reply seems to be on page 17....
(that was a dirty trick there, Johan!)
...
Dynamic paging ... :Whistling:
In my SSC-forum-profile I've set "Posts Per Page" to 50 :w00t:
Hence the faulty page ref...:blush:
The next time I should just refer to the forums page 1 .....
This way the click-reader will get the full message history as well...;)
Forgot all about that feature...hehe. There's supposedly some way to create direct links to posts, too, but I forget what that is right now.
Well - it was a good re-read anyway:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply