SmartPart SQL Connection

[ccshadow]

I'm using SmartPart to embed a 3rd party grid control into a SharePoint page. Everything went fairly smooth considering this is the first time I've attempted it. However, the grid isn't loading data.
I'm using SmartPart to embed a 3rd party grid control into a SharePoint page. Everything went fairly smooth considering this is the first time I've attempted it. However, the grid isn't loading data.

SQL Server is on one machine and the SharePoint site is on another. After creating the user control, I embedded it in a regular ASP.NET page and it worked. And the server setup was the same. I've tried tweaking the connection string (Trusted_Connection, Integrated Security) and just get a different error message in the trace file. From the error, it appears that the db user info isn't being parsed correctly.

Here's the connection string...

Dim conn As New SqlConnection("Server=WACBLSQL03;Database=WACommissions;User ID=WACommWebUser;Password=$com!aww.;Trusted_Connection=True;")

Here's the error from the SharePoint trace.axd...

System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Goals.GetGoals(Object sender, EventArgs e)

When I remove the Trusted Connection parameter from the connection string I get the following error message. It appears there's a problem with the grid DLL. Unfortunately, I don't know if this gets me closer to a solution or further away.

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
  at System.Security.CodeAccessSecurityEngine.SpecialDemand(PermissionType whatPermission, StackCrawlMark& stackMark)
  at System.Security.CodeAccessPermission.DemandInternal(PermissionType permissionType)
  at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)
  at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)
  at Obout.Grid.Grid.CompressDataSet(DataSet ds)
  at Obout.Grid.Grid.set_DataSource(Object value)
  at Goals.GetGoals(Object sender, EventArgs e)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The first permission that failed was:
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="SerializationFormatter"/>

The demand was for:
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="SerializationFormatter"/>

The granted set of the failing assembly was:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="Execution"/>
<IPermission class="System.Security.Permissions.StrongNameIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PublicKeyBlob="002400000480000094000000060200000024000052534
13100040000010001000FB8197053C40B57272D15F0BEEA2B9B088A21609
C78BE05CF1477EFE5FE8A5CD0CF6F45AE0C7A08E8BBACD59AD2A1D51D3F1
1D9B677FEF47A0918C5E9570B492A4D886314F58527DB3DC4AD4CCE1AAAF
86115A3228CCF1CC39355A329F375CBCE41E214ACDD9C36752EC54A607EF
8565B30AD09600FE1993B7660C8AE90A3C0"
Name="obout_Grid_NET"
AssemblyVersion="2.0.0.0"/>
<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Url="file:///C:/Inetpub/[URL unfurl="true"]wwwroot/wss/VirtualDirectories/devwacom/bin/obout_Grid_NET.DLL"/>[/URL]
<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Zone="MyComputer"/>
<IPermission class="System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Level="Minimal"/>
<IPermission class="Microsoft.SharePoint.Security.WebPartPermission, Microsoft.SharePoint.Security, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
version="1"
Connections="True"/>
</PermissionSet>

The assembly or AppDomain that failed was:
obout_Grid_NET, Version=2.0.0.0, Culture=neutral, PublicKeyToken=5ddc49d3b53e3f98
The method that caused the failure was:
Byte[] CompressDataSet(System.Data.DataSet)
The Zone of the assembly that failed was:
MyComputer
The Url of the assembly that failed was:
file:///C:/Inetpub/wwwroot/wss/VirtualDirectories/devwacom/bin/obout_Grid_NET.DLL

 

[philhege]

Ah, hops. They're great for beer, but stink for remote SQL security.

Have you tried adding impersonation to the .config file of the application? This is necessary to pass the client's credentials to the remote SQL box.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.