"Steven Church" <StevenChu***@discussions.microsoft.com> wrote in message
news:30B8AC3A-1FEE-481E-A6EF-82AC55AAC440@microsoft.com...
> I'm looking for assistance in accessing SQL databases through ASP pages.
> I've tried ODBC connections, DSN-less connections.
> I want to use integrated security but I can't seem to find a connection
> string that allows me access.
>
> My end goal is for IIS to serve up a .asp page, browsable by all users,
> that
> pulls data from 2 SQL Sources:  Dynamics GP and Dynamics CRM.
> If I change the web site configuration to run as a specific user instead
> of
> IUSR_...
> I get proper results from CRM.  I'm pretty sure this isn't the correct
> solution.
>
> Each source presents a different security challenge:  GP has it's own user
> login's separate from the network login; for CRM I want to ensure the
> application security model is followed so that users can only see records
> associated with their business unit.
>
> Here is my latest attempts at connection strings:
> 1. Dynamics CRM (get server errors)
> strConnect = "Provider=SQLOLEDB;" & _
>        "Data Source=CRMSRV1;" & _
>        "Initial Catalog=Company__MSCRM;" & _
>        "Integrated Security=SSPI"
> set conn = CreateObject("ADODB.Connection")
> conn.Open strConnect
>
> 2.  Dynamics GP  (I don't want to have to specify userid and password)
> conn.Open "Provider=sqloledb;" & _
>           "Server=ACCTSRV;" & _
>           "Database=TEST;" & _
>           "User Id=dynsa;" & _
>           "Password=access"
> set rs = Server.CreateObject("ADODB.recordset")
> sql="SELECT * FROM RM00100"
> rs.Open sql, conn
>
> My workarounds that I'm considering are:
> - each user runs his own web server and the web server runs as that
> particular user instead of IUSR_... (works for CRM, not for GP)
>
> - hard code userids and passwords into the .asp pages and deny access to
> the
> source code.  (less than ideal)
>
> Thank you in advance for any help you can provide.
>

"Ken Schaefer" wrote:

> Hi,
>
> I'm not familiar with how these two databases work, so you'll have to fill
> me in on these details. However the problems you seem to be running into are
> generic authentication/authorization issues, so we should be able to sort it
> out.
>
> The CRM database uses Windows logins - is that correct? And you want to
> authenticate the user to your web app, and pass those credentials (using a
> Trusted Connection) back to the CRM database?
>
> If so, you will need to authenticate the user somehow at the front end. Your
> options are Basic Auth, or Kerberos (part of IWA). Which of these two are
> you using?
>
> For the GP database, you are saying that you have a custom user identity
> that only GP knows about (i.e. separate from the user's Windows identity)?
> If so, how are you collecting this username/password combination from the
> end user?
>
> Cheers
> Ken
>
> --
> My IIS Blog: www.adOpenStatic.com/cs/blogs/ken
>
>
>
> "Steven Church" <StevenChu***@discussions.microsoft.com> wrote in message
> news:30B8AC3A-1FEE-481E-A6EF-82AC55AAC440@microsoft.com...
> > I'm looking for assistance in accessing SQL databases through ASP pages.
> > I've tried ODBC connections, DSN-less connections.
> > I want to use integrated security but I can't seem to find a connection
> > string that allows me access.
> >
> > My end goal is for IIS to serve up a .asp page, browsable by all users,
> > that
> > pulls data from 2 SQL Sources:  Dynamics GP and Dynamics CRM.
> > If I change the web site configuration to run as a specific user instead
> > of
> > IUSR_...
> > I get proper results from CRM.  I'm pretty sure this isn't the correct
> > solution.
> >
> > Each source presents a different security challenge:  GP has it's own user
> > login's separate from the network login; for CRM I want to ensure the
> > application security model is followed so that users can only see records
> > associated with their business unit.
> >
> > Here is my latest attempts at connection strings:
> > 1. Dynamics CRM (get server errors)
> > strConnect = "Provider=SQLOLEDB;" & _
> >        "Data Source=CRMSRV1;" & _
> >        "Initial Catalog=Company__MSCRM;" & _
> >        "Integrated Security=SSPI"
> > set conn = CreateObject("ADODB.Connection")
> > conn.Open strConnect
> >
> > 2.  Dynamics GP  (I don't want to have to specify userid and password)
> > conn.Open "Provider=sqloledb;" & _
> >           "Server=ACCTSRV;" & _
> >           "Database=TEST;" & _
> >           "User Id=dynsa;" & _
> >           "Password=access"
> > set rs = Server.CreateObject("ADODB.recordset")
> > sql="SELECT * FROM RM00100"
> > rs.Open sql, conn
> >
> > My workarounds that I'm considering are:
> > - each user runs his own web server and the web server runs as that
> > particular user instead of IUSR_... (works for CRM, not for GP)
> >
> > - hard code userids and passwords into the .asp pages and deny access to
> > the
> > source code.  (less than ideal)
> >
> > Thank you in advance for any help you can provide.
> >
>
>
>

"Steven Church" <StevenChu***@discussions.microsoft.com> wrote in message
news:01257C06-02DF-4B07-8BD4-9F7D60D1A0D8@microsoft.com...
> Hi Ken,
>
> Yes, I believe the problems are general authentication issues.
> CRM does use/require a Windows login and those are the credentials I want
> to
> pass to the CRM database.  The trusted connection strings I've tried don't
> seem to work.  I was using basic authentication.
> One of the things I've tried was to create a CRM user account for IUSR_...
> but that didn't help either.  (If I change the security settings on the
> web
> server to run as my personal account, everything is fine.  To my knowledge
> the IUSR... and my personal CRM user are set up identically.)

>
> My application is essentially a "dashboard" type application, and really
> it's only one or two pages of summary statistics.  How many CRM accounts?
> How many GP accounts.  Which accounts are not synced?   That type of
> thing.
>
> My expectation was that once I've set up the ODBC connection to the SQL
> server that I could simply reference that ODBC and extract the data.
>
> ...Steve
>
> "Ken Schaefer" wrote:
>
>> Hi,
>>
>> I'm not familiar with how these two databases work, so you'll have to
>> fill
>> me in on these details. However the problems you seem to be running into
>> are
>> generic authentication/authorization issues, so we should be able to sort
>> it
>> out.
>>
>> The CRM database uses Windows logins - is that correct? And you want to
>> authenticate the user to your web app, and pass those credentials (using
>> a
>> Trusted Connection) back to the CRM database?
>>
>> If so, you will need to authenticate the user somehow at the front end.
>> Your
>> options are Basic Auth, or Kerberos (part of IWA). Which of these two are
>> you using?
>>
>> For the GP database, you are saying that you have a custom user identity
>> that only GP knows about (i.e. separate from the user's Windows
>> identity)?
>> If so, how are you collecting this username/password combination from the
>> end user?
>>
>> Cheers
>> Ken
>>
>> --
>> My IIS Blog: www.adOpenStatic.com/cs/blogs/ken
>>
>>
>>
>> "Steven Church" <StevenChu***@discussions.microsoft.com> wrote in message
>> news:30B8AC3A-1FEE-481E-A6EF-82AC55AAC440@microsoft.com...
>> > I'm looking for assistance in accessing SQL databases through ASP
>> > pages.
>> > I've tried ODBC connections, DSN-less connections.
>> > I want to use integrated security but I can't seem to find a connection
>> > string that allows me access.
>> >
>> > My end goal is for IIS to serve up a .asp page, browsable by all users,
>> > that
>> > pulls data from 2 SQL Sources:  Dynamics GP and Dynamics CRM.
>> > If I change the web site configuration to run as a specific user
>> > instead
>> > of
>> > IUSR_...
>> > I get proper results from CRM.  I'm pretty sure this isn't the correct
>> > solution.
>> >
>> > Each source presents a different security challenge:  GP has it's own
>> > user
>> > login's separate from the network login; for CRM I want to ensure the
>> > application security model is followed so that users can only see
>> > records
>> > associated with their business unit.
>> >
>> > Here is my latest attempts at connection strings:
>> > 1. Dynamics CRM (get server errors)
>> > strConnect = "Provider=SQLOLEDB;" & _
>> >        "Data Source=CRMSRV1;" & _
>> >        "Initial Catalog=Company__MSCRM;" & _
>> >        "Integrated Security=SSPI"
>> > set conn = CreateObject("ADODB.Connection")
>> > conn.Open strConnect
>> >
>> > 2.  Dynamics GP  (I don't want to have to specify userid and password)
>> > conn.Open "Provider=sqloledb;" & _
>> >           "Server=ACCTSRV;" & _
>> >           "Database=TEST;" & _
>> >           "User Id=dynsa;" & _
>> >           "Password=access"
>> > set rs = Server.CreateObject("ADODB.recordset")
>> > sql="SELECT * FROM RM00100"
>> > rs.Open sql, conn
>> >
>> > My workarounds that I'm considering are:
>> > - each user runs his own web server and the web server runs as that
>> > particular user instead of IUSR_... (works for CRM, not for GP)
>> >
>> > - hard code userids and passwords into the .asp pages and deny access
>> > to
>> > the
>> > source code.  (less than ideal)
>> >
>> > Thank you in advance for any help you can provide.
>> >
>>
>>
>>