Connect to SQLServer 2005 on different server

[BenM]

I have to connect to two SQLServers in my script. One server is local, the
other is remote.

I am using ODBC for the local connection:
pasConnStr = "Driver={SQL Server};" & _
"Server=(local);" & _
"DataBase=PAS"

And I have tried various connections to the remote server, this one happens
to be oledb:
crmConnStr = "Provider=sqloledb;" & _
"Data Source=CRM;" & _
"Initial Catalog=ULGT_MSCRM" & _
"User ID=CRM\IUSR_CRM;" & _
"Password="


The first server name is 'SQL1', and the second server name is 'CRM'. So, I
can connect just fine to the local server running on SQL1, but I cannot
connect to CRM. I have tried everything I can think of, including adding the
anonymous IIS login, as my connection string shows above.

The current connection string returns the wonderful error:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'CRM\IUSR_CRM'

So, I am at a loss. The remote server does not have a static IP, at this
time anyway.

 

[Bob Barrows [MVP]]

I have to connect to two SQLServers in my script. One server is
local, the other is remote.

I am using ODBC for the local connection:
pasConnStr = "Driver={SQL Server};" & _
"Server=(local);" & _
"DataBase=PAS"
http://www.aspfaq.com/show.asp?id=2126


And I have tried various connections to the remote server, this one
happens to be oledb:
crmConnStr = "Provider=sqloledb;" & _
"Data Source=CRM;" & _
"Initial Catalog=ULGT_MSCRM" & _
"User ID=CRM\IUSR_CRM;" & _
"Password="



The first server name is 'SQL1', and the second server name is 'CRM'.
So, I can connect just fine to the local server running on SQL1, but
I cannot connect to CRM. I have tried everything I can think of,
including adding the anonymous IIS login, as my connection string
shows above.

The current connection string returns the wonderful error:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'CRM\IUSR_CRM'

Assuming you've simply censored the password, it appears that your second
server is not set up for "mixed" security.
See http://www.aspfaq.com/show.asp?id=2138, which links to
http://support.microsoft.com/kb/176380/EN-US/

So, I am at a loss. The remote server does not have a static IP, at
this time anyway.

Nothing to do with the IP. The server is being found: it's the login attempt
that is failing.

 

[BenM]

I appreciate your response up to this point.

Upon your advice, I changed my initial ODBC connection to OLEDB. I also
read the links and changed my code to the following for the 2nd connection
string:

crmConnStr = "Provider=sqloledb;" & _
"Data Source=CRM;" & _
"Initial Catalog=ULGT_MSCRM;" & _
"Integrated Security=SSPI"

The SQL Server was already in mixed authentication mode. I have the
IUSR_CRM setup on the CRM server and the Database. Now I get the following
error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Thanks in advance!

 

[Bob Barrows [MVP]]

I appreciate your response up to this point.

Upon your advice, I changed my initial ODBC connection to OLEDB. I
also read the links and changed my code to the following for the 2nd
connection string:

crmConnStr = "Provider=sqloledb;" & _
"Data Source=CRM;" & _
"Initial Catalog=ULGT_MSCRM;" & _
"Integrated Security=SSPI"

The SQL Server was already in mixed authentication mode. I have the
IUSR_CRM setup on the CRM server and the Database. Now I get the
following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Did you follow the link in http://support.microsoft.com/kb/176380/EN-US/ to
this article? http://support.microsoft.com/kb/176379/en-us

 

[BenM]

Since this is on a remote server, I found that the IUSR_machineName would not
work.

I created a basic login on the SQL Server itself, and now I can connect fine.