ADO.net Connection Failure From Web Server - Works From Development Machine

[RADical_Systems]

I am posting this for the web developer of one of our clients so will take a little time to respond to any questions on it as I get the details from him - please bare with me.

He has connected to the SQL Anywhere 10 database of our shared client from his development machine but cannot make the connection from the web server that will host the site and can't work out why.

The connection string is, (it has < and /> at the start and end but then it would not show on the preview even with <![CDATA[):

add name="sybaseSpaceManager" connectionString="ENG=WebTest;database=webtest;LINKS=tcpip(Host=XX.XX.XX.XX; ServerPort=2639);UID=xxxxxxxx;PWD=xxxxxxxx" providerName="iAnywhere.Data.SQLAnywhere"

The error being returned is:

System.TypeInitializationException: The type initializer for 'iAnywhere.Data.SQLAnywhere.SAConnection' threw an exception. ---> System.Security.SecurityException: Request failed. at iAnywhere.Data.SQLAnywhere.SAConnection..cctor() --- End of inner exception stack trace --- at iAnywhere.Data.SQLAnywhere.SAConnection..ctor(String connectionString) at SYBASE_Default.Page_Load(Object sender, EventArgs e) in E:\\Domains\\v\\vikingselfstorage.co.uk\\user\\htdocs\\SYBASE\\Default.aspx.vb:line 21

Apart from being on different networks in different locations the only real difference we can see in terms of the systems is that on his development machine he has Sybase 10 installed. Would it need to be on the server as well? I am being told that the server has all the pre-requesites for Sybase ADO connectivity but I can't check that as I don't even know what that means.

The server hosts have apparently done some tests and have said:

"Our engineers have checked your script and it appears that the connection string is attempting to do something that is in violation of the trust environment. We recommend that you contact Sybase for assistance."

To me that smacks of "we don't know and made something up that sounds like it isn't at our end". As we know the process works from his development machine then it would seem to me that the issue is at the server not on the Sybase end.

Anyone got any experience or words of wisdom for things to try, test or add onto the web server, (we can't install a full SQL Anywhere as it is a shared server).

Thanks in advance. Alasdair

[Breck_Carter]

Is there anything interesting at line 21 of Default.aspx.vb?

[RADical_Systems]

Breck, this is the script that calls the connection string and then fails. I don't know if it is interesting or not. This is Line 21:

  Dim SAConn As New SAConnection(conStr)

[Vlad]

Hm, I cannot say that I know the right answer, but this sounds that the issue is related to Trust settings of IIS: https://help.webcontrolcenter.com/kb/a1110/how-do-i-change-my-websites-trust-level-_net-windows.aspx Just guessing. Please try the full trust!

I can try to reproduce the issue, but I need to know how the application is hosted. Is it a local IIS, a developer version, a remote IIS?...

[jeff_albion · Accepted solution]

As part of the instantiation of the SQL Anywhere ADO.NET assembly, we will attempt to extract a native DLL (dbdata10.dll on a SQL Anywhere 10 client) for communication to SQL Anywhere from the managed code to unmanaged code via PInvoke (see the documentation). It would appear from the error that you are not currently allowed in the ASP.NET context to do that, due to the server's security restrictions.

There is an article from Microsoft to adjust the security settings for your ASP.NET application which may help: https://support.microsoft.com/en-us/kb/555466

[RADical_Systems · Accepted solution]

Jeff, thanks for that. In the face of this information, (and Vlad's reply), the hosting provider has admitted that the server is set with a Medium Trust level thus this won't work.

As a supplementary do you know if there might be a way round the issue by using maybe ODBC to connect. If so any idea of the driver, 5.3 Unicode has been mentioned but this seems to be MySQL), and any resources on such.

Thanks again everyone.

[jeff_albion · Accepted solution]

As a supplementary do you know if there might be a way round the issue by using maybe ODBC to connect.

If your hosting provider allows it, there is a SQL Anywhere ODBC driver that can be installed:

http://scn.sap.com/docs/DOC-35857

http://dcx.sap.com/index.html#1001/en/dbpgen10/pg-odbc-client-deploy.html

You can either use the full client installer or see the documentation for the manual deploy options to just copy the required ODBC files, then run regsvr32 dbodbc10.dll to register the ODBC driver with the system. Once the ODBC driver is available, you can then use OdbcConnection:

https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection%28v=vs.110%29.aspx

[RADical_Systems · Accepted solution]

Thanks again Jeff. I am sure this is the end of it!