ASP connect to SQL server 2005

[webber1]

I've been googling for a few days and have tried all kinds of connection strings, but none have worked so far. I have a webpage on one server (using ASP NOT ASP.NET) trying to connect to a SQL database on another server (SQL 2005 server). I tried connecting using .net on another page and I just added a SqlDataSource tool and that works so I know there isn't a block between the servers. I think it must be my connection string.

Here are some of the connection strings I've tried (they all have different error messages and error out at the conn.open):

set conn=Server.CreateObject("ADODB.Connection")
cst = "Driver={SQL Server};Server=111.111.11.11;Database=dbname;UID=domain\user;PWD=password"
conn.Open(cst)

------------------

conn.ConnectionString="driver={SQL Server};server=servername;uid=domain\user;pwd=password;"
conn.Open
conn.DefaultDatabase="dbname"

------------------

conn.Open "Provider=SQLNCLI;Server=111.111.11.11;Database=dbname;UID=domain\user;PWD=password;"
-----------------
conn.ConnectionString="Driver={SQL Server};Server=111.111.11.11;Initial Catalog=dbname;User Id=domain\user;Password=password;Trusted_Connection=True;"
conn.Open
---------------------
conn.Open "Provider=SQLOLEDB; Data Source = 111.111.11.11; Initial Catalog = dbname; User Id = domain\user; Password=password;"
----------
conn.Open "Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=domain\user;Initial Catalog=dbname;Data Source=servername"
-----------------------
cst = "Provider=SQLOLEDB;" & _ 
        "Data Source=111.111.11.11;" & _ 
        "Initial Catalog=dbname;" & _ 
        "Network=DBMSSOCN;" & _ 
        "User Id=domain\user;" & _ 
        "Password=password" 
conn.open cst

The last connection string has this error:
Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user 'domain\user'.

-------------

maybe it is a server setting, but I have looked on the SQL server management studio and that user is in the security list and I can log on to the server using that username and password. The ip on here is not real, but I made sure it is correct on the real page.

I would appreciate any kind of advice or suggestion. Thank you!

[ArtistScope]

Is that the Express (Free) version?

[webber1]

Is that the Express (Free) version?

Hello. It's not express.

[ArtistScope]

This one used to work for me in a similar scenario...

strCon = "Provider=SQLOLEDB;Server=" & strServerName & ";User ID=" & strUserName & ";Password=" & strPassword & ";Database=" & strDatabaseName & ";"

The Server name could be an IP address or if it's a name then it needs to added to the server's ODBC list.

Or the problem could be a permissions thing... these days we are using SQL 2005 Express and I can never get them set up without calling in a partner :-)

[webber1]

Thank you ArtistScope! I tried the string and still get "Login failed for user". Yeah..it must be a permissions thing somewhere...

[ArtistScope]

Maybe it's the account type that you trying to access with?

I think it needs to be for remote use. On ours we use "sa" as the remote user which is not an administrator on the SQL server. But this is the part I never got right either.

[webber1]

Maybe it's the account type that you trying to access with?

I think it needs to be for remote use. On ours we use "sa" as the remote user which is not an administrator on the SQL server. But this is the part I never got right either.

So you made an account and set that account in SQL Server Management Studio to have the sysadmin role?

[meganjo]

I think you have to set the connection as a trusted site in the application properties.

[webber1]

Hello. Thank you for all the suggestions. Where do I find the application properties?

I went to connectionstrings.com and tried these two strings from that site for trusted connections:
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
and
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

They both get errors:
"Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;"
gets this error:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

"Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;"
gets this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I'm googling the errors now so I will repost if find a solution. But please let me know if someone has advice. Thank you!

[ArtistScope]

I think that it may mean a "trusted site" or user in the SQL server properties.

[G_Waddell]

Hi,

I'd do a double check here if I were you:
http://www.connectionstrings.com
It lists out loads of ways of connecting to different datasources...

[achinaseller]

You'd better ask room service, I used to like this solution