2007년 11월 28일 수요일 오후 9:44I created a Webservice, with a query to the table "Subject" to read nodes, the problem is the connection string, if that enabled "Integrated Security = sspi" does not work, but if enable "uid = xxxx; pwd = yyyy", it works, in this case I had to create an account with SQL, i could leave it .... But I need all the security is managed by Windows, someone knows that does not work "Integrated Security = sspi"? (attached code)Code Block
PrivateFunction CRM_Ruta(ByVal sNodo As String, ByVal sRuta As String) As String Dim cn As New ADODB.Connection() Dim rs As New ADODB.Recordset() Dim cnStr As String Dim sPSubject As String Dim sTitle As String Dim sSubjectid As String Dim cmd As New ADODB.Command()
Servidor = ConfigurationManager.AppSettings("Servidor")
BaseDatos = ConfigurationManager.AppSettings("BasedeDatos") 'this don´t work, Why? 'cnStr = "Provider=SQLOLEDB;Server=" + Servidor + ";Database=" + BaseDatos + ";Integrated Security=sspi"
cnStr ="Provider=SQLOLEDB;Server=" + Servidor + ";Database=" + BaseDatos + ";uid=xxxxxx;pwd=yyyyyy"
cn.ConnectionString = cnStr
rs = cn.Execute("SELECT parentsubject,title,subjectid FROM [" + BaseDatos + "].[dbo].[Subject] WHERE subjectid = '" + sNodo + "'") While Not (rs.EOF)
sPSubject = rs.Fields(0).Value.ToString()
sTitle = rs.Fields(1).Value.ToString()
sSubjectid = rs.Fields(2).Value.ToString()
rs.MoveNext()End While If sRuta.Length = 0 Then
sRuta = sTitleElse
sRuta = sRuta +"|" + sTitle End If
cn =Nothing If sPSubject = "" Then Return sRuta End If Return CRM_Ruta(sPSubject, sRuta) End Function
2007년 11월 29일 목요일 오전 9:23소유자
Are you using FilterView? If yes, you have to give a crm user account to do the sql query.
2007년 11월 29일 목요일 오후 2:03
Jim Wang wrote:
Are you using FilterView? If yes, you have to give a crm user account to do the sql query.Initially i using FilterView, then read that they had security, i tried anyway the query and does not work, then change the query directly to the tables and neither works, it works only when i change the "connection string" with login and password.This violate the security and structure of CRM ?Is there another way to do it?
2007년 11월 29일 목요일 오후 5:38중재자What error do you get ? And what account does the code run under ? The supported way is to run the code under an account of a CRM user, use integrated security and query filtered views. Note that the only SQL permissions in the MSCRM database (other than those of sa or dbo) are Select for each filtered view for crm users
2007년 11월 30일 금요일 오후 2:50
DavidJennaway wrote: What error do you get ? And what account does the code run under ? The supported way is to run the code under an account of a CRM user, use integrated security and query filtered views. Note that the only SQL permissions in the MSCRM database (other than those of sa or dbo) are Select for each filtered view for crm usersThe error message is not delivered much information is as follows:
- "Error. For more information, contact the system administrator"
- Has run the Web service that is a valid account in the domain with access to CRM and role of "system administrator"
Now, I have read that this is a very common problem, in any way, do not appear solutions or examples, only speculation, in the book "Working With, Microsoft Dynamics CRM 3.0" recommend occupy the SQL function "fn_FindUserGuid" when making querys to filtered views, but there is no explicit example, while I still investigating and testing.