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.

[ Post a follow-up to this message ]

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" <StevenChurch@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.
>

[ Post a follow-up to this message ]