로그인
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Problem with connection string in Webservice

Answered Problem with connection string in Webservice

  • 2007년 11월 28일 수요일 오후 9:44
     
     
    로그인하여 투표
    0
    I 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

    Private Function 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()

     

     

    Dim Servidor, BaseDatos As String

    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

    cn.Open(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 = sTitle

    Else

    sRuta = sRuta + "|" + sTitle

    End If

    rs.Close()

    cn.Close()

    rs = Nothing

    cn = Nothing

    If sPSubject = "" Then

    Return sRuta

    End If

    Return CRM_Ruta(sPSubject, sRuta)

    End Function

     

     

    Atte.

    Manuel Arriagada

     

모든 응답

  • 2007년 11월 29일 목요일 오전 9:23
    소유자
     
     
    로그인하여 투표
    0

    Are you using FilterView? If yes, you have to give a crm user account to do the sql query.

     
  • 2007년 11월 29일 목요일 오후 2:03
     
     
    로그인하여 투표
    0
     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
    중재자
     
     답변됨
    로그인하여 투표
    0
    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
     
     
    로그인하여 투표
    0

     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 users

     

     

    The 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.