Posting XMLA to AS from java without msmdpump.dll

I'm simply trying to invoke an Analysis Server CLR assembly procedure using MDX from a non windows machine in a a java environment. I also wish to require as little configuration on the SQL Server machine as possible.

Posting the request via HttpURLConnection as below works.

url = new URL("http://localhost//olap//msmdpump.dll ");
HttpURLConnection con = (HttpURLConnection) url.openConnection();

The question is I'd prefer not to have to require IIS plus the pump to be running on the AS server(msmdpump.dll). We know MS code is sending XMLA to the AS Database.

Why can we not use the same channel? It may not be http but I do not care. I'd rather have minimum stuff to do on the server.

Thanks in advance

Zub

I'd say in this stiuation you are probably better off using HTTP. On windows machine you can use naitive AS client : ADOMD.NET or AS OLEDB, and establish connection using TCP/IP , but coming from outside you will face quite a few problems. Configuring virtual direcory compared to ... for instance trying to figure out how to authenticate your non-windows user if by far easier task.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Possible to encrypt database assembly?

Hello.

I've built a simple Visual Basic .NET project containing the following code...

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub WhoAmI()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT SUSER_SNAME()", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class

From Visual Studio, I want to encrypt the contents of this assembly, as a proof-of-concept.
Even though assembly contents are stored as varbinary(MAX) in the database, converting to varchar(MAX) will expose the code.

However, the Dotfuscator Community Edition reports the following error: "Dotfuscator Community Edition cannot operate on SQL Server applications.... please try Standard or Professional Edition."

Has anyone tried encrypting a database assembly and deploying to the database?

A good test would be to issue the following TSQL script against the database holding the assembly...

-- Does the sample code above run?
EXEC dbo.WhoAmI
GO
-- Is the code readable?
SELECT * , Convert(varchar(MAX), content) FROM sys.assembly_files

Hi,

There is no explicit support for encrypted assemblies in the database. You can use a code obfuscation utility to make reverse-engineering difficult, as you suggest.

Perhaps someone else has a favorite obfuscator they can recommendI don't know much about them.

Cheers,

-Isaac

|||

Note that the code you're reading from sys.assembly_files isn't related to encrypting the assembly - VS deploys your full source code and PDB files to the database to assist with debugging. If you don't want VS to do this, then you can turn it off under the project properties / Deploy / Deploy Code option. This still won't protect your assembly though as anyone can use a reflection tool such as http://www.denisbauer.com/NETTools/SQL2005Browser.aspx. Obfuscation can help here, but the real solution is to control who has access to the system views containing the assembly using metadata permissions.

Steven

|||A great answer... You've given lots of new leads. Thanks.

Possible Concurrency Issues.

Hi,
I was wondering if it is possible to call a stored procedure from sql server 2005 (call it sp_1) that calls an assembly which takes a message, wraps it in soap and calls a webservice and waits for a reply from that webservice (the stored procedure is clr not t-sql). This WebService needs to then call sp_1 to perform some other tasks. Is this possible or does sp_1 need to have finished what it was doing before it can be called again.

I have been trying to do this and have received a number of errors one of which looks like;

'The context connection is already in use.'

Sorry if I haven't worded it very well, I will try to clear up any questions if you need me to.

Thanks
N
Dont worry about replying to this, I have done some more research and the I was trying to open multiple context connections when only 1 is allowed at a time, which was creating the error.