Member Avatar for 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!

  • 5 Contributors
  • 11 Replies
  • 256 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by achinaseller

Recommended Answers

All 11 Replies

Member Avatar for ArtistScope

Is that the Express (Free) version?

Member Avatar for webber1

Is that the Express (Free) version?

Hello. It's not express.

Member Avatar for 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 :-)

Edited by ArtistScope because: n/a
Member Avatar for webber1

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

Member Avatar for 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.

Member Avatar for 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?

Member Avatar for meganjo

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

Edited by Ezzaral because: Snipped "fake sig" link. Please restrict such links to your site-wide user signature, which can be edited from the user control panel.
Member Avatar for 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!

Member Avatar for ArtistScope

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

Member Avatar for achinaseller

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.