locked
'The remote name could not be resolved: 'api.microsofttranslator.com'' executing SQL Server 2008 R2 CLR function with HTTP request to Bing Translator API RRS feed

  • Question

  • Hi all,

    I am trying to make working a CLR function for SQL Server 2008 R2 which using HTTP request to Bing Translator API.

    ---

        public partial class UserDefinedFunctions
        {
            [Microsoft.SqlServer.Server.SqlFunction]
            public static SqlString CLRTranslate(
                string text,    // "Translate this for me"
                string from,    // "en"
                string to)      // "fr"
            {
                SqlString translation;
                string appId = "91356235F10D5074C93A8C76E8A5923D113AD76B";

                string uri = "http://api.microsofttranslator.com/v2/Http.svc/Translate?appId=" + appId +
                    "&text=" + text + "&from=" + from + "&to=" + to;

                WebRequest req = System.Net.WebRequest.Create(uri);
                WebResponse resp = req.GetResponse();
                Stream strm = resp.GetResponseStream();
                StreamReader reader = new System.IO.StreamReader(strm);
                translation = reader.ReadToEnd();

                return translation;
            }
        };

    ---

    During execution I get this error:

    ---

    Debugging script from project script file.

    Auto-attach to process '[6048] [SQL] ivanko' on machine 'ivanko' succeeded.
    The thread 'ivanko [61]' (0x1648) has exited with code 0 (0x0).
    The thread 'ivanko [61]' (0x1648) has exited with code 0 (0x0).
    The thread 'ivanko [62]' (0x1648) has exited with code 0 (0x0).
    The thread 'ivanko [62]' (0x1648) has exited with code 0 (0x0).
    The thread 'ivanko [62]' (0x1648) has exited with code 0 (0x0).
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'CLRTranslate', Symbols loaded.
    Auto-attach to process '[6048] sqlservr.exe' on machine 'ivanko' succeeded.
    Step into: Stepping over non-user code 'string.Concat'
    Step into: Stepping over non-user code 'System.Net.WebRequest.Create'
    'IVANKO;.Net SqlClient Data Provider;6688' (Managed (v2.0.50727)): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'
    Column1                                                                                                                                      
    ---------------------------------------------------------------------------------------------------------------------------------------------
    To run your project, please edit the Test.sql file in your project. This file is located in the Test Scripts folder in the Solution Explorer.
    No rows affected.
    (1 row(s) returned)
    A .NET Framework error occurred during execution of user-defined routine or aggregate "CLRTranslate":
    System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
    System.Security.SecurityException:
       at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
       at System.Security.CodeAccessPermission.Demand()
       at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
       at System.Net.HttpRequestCreator.Create(Uri Uri)
       at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
       at UserDefinedFunctions.CLRTranslate(String text, String from, String to)
    .
    A first chance exception of type 'System.Security.SecurityException' occurred in mscorlib.dll
    The thread 'ivanko [62]' (0x1648) has exited with code 0 (0x0).
    The thread '<No Name>' (0x1648) has exited with code 0 (0x0).
    The program '[6048] [SQL] ivanko: ivanko' has exited with code 0 (0x0).
    The program '[6048] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).
    ---

    Or if execute from SSMS ("select dbo.CLRTranslate('this is the text','en','fr');") I got this:

    ---

    Msg 6522, Level 16, State 2, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "CLRTranslate":
    System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
    System.Security.SecurityException:
       at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
       at System.Security.CodeAccessPermission.Demand()
       at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
       at System.Net.HttpRequestCreator.Create(Uri Uri)
       at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
       at UserDefinedFunctions.CLRTranslate(String text, String from, String to)

    ---

    Please help me to solve this problem...

    • Edited by Ivan Kosyakov Tuesday, February 1, 2011 6:28 PM found part of solution, so changed title
    Tuesday, February 1, 2011 10:07 AM

All replies

  • Then I followed recommendations from this page http://nathondalton.wordpress.com/2010/01/07/get-geospatial-from-web-service-in-sql/ :

    • Things to consider with this are to ensure you configure the Permission Level to External by selecting the Database tab in the project properties and selecting External from the Permission level drop down.
    • Ensure you strong name the assembly by running “sn.exe /k KEYNAME.snk” at the command line, place this file in your project directory and in the Signing tab of the project properties, click Sign the assembly, choose the file from the drop down and save project. This signs and strong names the assembly.
    • In the project set SqlFunction attribute to “[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]“.

    Then changed ownership on the database: 

    exec sp_changedbowner 'domain\login'

     

    Now I am getting this error:

     

    ---

    A .NET Framework error occurred during execution of user-defined routine or aggregate "CLRTranslate":
    System.Net.WebException: The remote name could not be resolved: 'api.microsofttranslator.com'
    System.Net.WebException:
       at System.Net.HttpWebRequest.GetResponse()
       at UserDefinedFunctions.CLRTranslate(String text, String from, String to)
    .
    A first chance exception of type 'System.Net.WebException' occurred in System.dll
    The thread 'ivanko [60]' (0x1738) has exited with code 0 (0x0).
    The thread '<No Name>' (0x1738) has exited with code 0 (0x0).
    The program '[6048] [SQL] ivanko: ivanko' has exited with code 0 (0x0).
    The program '[6048] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).

    ---

    Tuesday, February 1, 2011 6:12 PM
  • Ivan,

    Can you test whether your machine can resolve "api.microsofttranslation.com" into an IP from a Windows command line, e.g. "ping api.microsofttranslation.com"?

    If it works, it may be due to you don't have network access permission in your UDF. Would be helpful if you write a simple UDF which tries to access a known website to determine whether it is a network access permission issue.

    Friday, February 11, 2011 1:08 AM