.asp access to Dynamics SQL databases

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;" & _
"ServerCTSRV;" & _
"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.

Re: .asp access to Dynamics SQL databases

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" 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;" & _
> "ServerCTSRV;" & _
> "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.
>

Re: .asp access to Dynamics SQL databases

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

On the GP side, I really didn't want to collect username/password from the
users.

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" 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;" & _
> > "ServerCTSRV;" & _
> > "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.
> >
>
>
>

Re: .asp access to Dynamics SQL databases

"Steven Church" 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.)

Hi,

When you use that particular Trusted Connection to the CRM database, what
error do you get?

Additionally, if the web pages are ASP.NET, have you set the impersonate="true" /> in your web.config to ensure that ASP.NET is
impersonating the end user?


> On the GP side, I really didn't want to collect username/password from the
> users.

So what account do you want to use? Or do you want to put a fixed
username/password in a ODBC DSN?

Cheers
Ken


>
> 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" 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;" & _
>> > "ServerCTSRV;" & _
>> > "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.
>> >
>>
>>
>>