ASP/SQL Error 80040e4d

I have an ASP application that has been running for years where it accesses a remote SQL Server and retrieves data. Unfortunately, the server was turned off, the database moved, without telling me. I am now trying to recreate the access to the new location of the SQL Server. The SQL Server is setup with mixed mode authentication. I have created a Login called cewebuser and given it the password of letmein. The following is my ASP code:

connectionString = "Provider=SQLOLEDB;Server=216.70.241.178;Database=vwiexport;User ID=cewebuser;Password=letmein"
Set checkVWI = Server.CreateObject("ADODB.Connection")
checkVWI.Open connectionString

All I get is:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'cewebuser'.

I have mapped port 1433 in my router so that the SQL Server requests are routed through to the server.

How can I make this work?

Thanks.
wayneh13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
When you created the Login, how did you do it?

In general, you need to create the Login from the <Server Name>\Security\Logins folder. (This is the "path" in SQL Server Management Studio. I believe Enterprise Manager uses a different naming convention for the "folders".) When you do this, you provide the name of new user (cewebuser), select SQL Server authentication, enter the password (letmein), select the default database (vwiexport). You can also set the database roles as well. In SSMS, this is done in the User Mapping area. (Enterprise Manager has a similar area, and I believe it is on the main dialog window at the bottom.) In the User Mapping area, you select the database in the top section, and in the bottom, select the role the user will have in the database. In general, you want to grant the lowest permissions necessary. So if the new login will only read data, grant only the db_datareader permission. If the new login will also be modifying data, then grant db_datawriter as well.
0
wayneh13Author Commented:
In SQL Server Enterprise Manager, I went to the SQL Server Group, Server Name (VISION01\VWISQL), Security, Logins. I created the cewebuser login and gave it the letmein password under SQL Server Authentication. I specified vwiexport as the default database. I gave the login the read permissions as you had specified.
0
Shaun KlineLead Software EngineerCommented:
Another issue that occurs when a database is moved between DB servers is that the login IDs in the database are not added to the DB server (it's a security issue) as login names.

To determine if this is an issue you can do one of two things:
1.) Find the login ID in the security folder for the database, view the properties for the Login ID and check if the Login Name field is blank.
2.) Run "exec sp_change_users_login 'Report' " in a query window for your database. This will return any login IDs that are not sync'd with a db server login name.

If this is your issue, it can be corrected by doing either of these options:
1.) Delete and re-add the login id to the Database (under the security tab).
2.) Run "exec sp_change_users_login 'AUTO_FIX', '<Login ID>' " in a query window for your database.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

wayneh13Author Commented:
Actually the database was not moved. Previously it was "exposed" on our web server and was replicated with the existing SQL Server.

I ran the SQL command and no login IDs were returned.
0
wayneh13Author Commented:
So, I was just given the sa password from the system administrator. When I change from the cewebuser login to the sa login, my error changes to:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "vwiexport" requested by the login. The login failed.

Hopefully, this will expose the solution, either with the cewebuser account or with the sa account.
0
Shaun KlineLead Software EngineerCommented:
Let me rephrase what happened to your environment:
1.) You had a server running both a web server (guessing IIS) and SQL Server on the Internet.
2.) The data in your application's database was exported to another database on another server (that was behind a firewall?).
3.) SQL Server was turned off on this server.
4.) Now you are trying to access the data in the exported database?

Are you able to ping the new server from your web server? If you were connecting to the new database server from your web server, you should be able to log in to the database server, provided you were given the correct password for the SA account, as the SA account (at least in SQL Server 2000) is considered the database owner.

If there is a firewall (either via router or software) between your web server and your database server, I suggest verifying that it is truly allowing SQL calls between the two servers.
0
wayneh13Author Commented:
I have an ASP application running on a server located in Orange County. The SQL Server is located on another server in San Diego County.  The firewall that is setup is routing the SQL Server port to the server (port 1433).

I believe that we are clearly connecting to the SQL Server as I am getting different responses based upon using different login accounts, i.e. sa account vs. cewebuser account. For the cewebuser account I get "Login failed" but for the sa account, the response is "Cannot open database vwiexport". There is no other difference in the connection string other than changing the userid & password.
0
Shaun KlineLead Software EngineerCommented:
The Login Failed message would indicate that either the login ID does not exist on that server/instance or that the password is incorrect for cewebuser.  The second message would indicate that the server does not have the vwiexport database. Is it possible that the database server has multiple instances running on it? If that is the case, you would need to change your connection string to include the instance name: <server name/ip address>\<instance name>.
0
wayneh13Author Commented:
I get an error "Server not found" when I include the instance name as "a.b.c.d\instance name" where a.b.c.d is the ip address of the server.
0
Shaun KlineLead Software EngineerCommented:
You know, I didn't even notice this in an earlier comment from you: VISION01\VWISQL
I'm guessing that the internal server name that matches the IP address of 216.70.241.178 is Vision01.
And the instance name for the DB engine would then be VWISQL.

Did you try changing your connection string to the following (as you mentioned in your previous comment)?
connectionString = "Provider=SQLOLEDB;Server=216.70.241.178\VWISQL;Database=vwiexport;User ID=cewebuser;Password=letmein"
0
wayneh13Author Commented:
Yes, I had already tried changing the connectionString. I get the "Server not found" error message when I include the instance name.
0
Shaun KlineLead Software EngineerCommented:
I found this article on Microsoft's website regarding Firewall configuration: http://msdn.microsoft.com/en-us/library/ms175043.aspx
and I found this article about connecting to a SQL Server instance using the IP Address: http://support.microsoft.com/kb/265808

Between the two articles, it sounds like your VWISQL instance is "listening" on a port other than 1433 (the port you set your firewall up to allow traffic.) This would happen if the server is running two separate instances.

If your SQL Server machine is SS2K (which I'm guessing it is, since you are using Enterprise Manager) and you have access to the box, launch "SQL Server Network Utility". In the Instance(s) on this server, select VWISQL. In the Enabled protocols box, select TCP/IP and display the properties. This will tell you what port this instance is listening on.

If your SQL Server machine is SS2K5, launch "SQL Server Configuration Manager". Under "SQL Server 2005 Network Configuration", select "Protocols for VWISQL". On the right side, right click "TCP/IP" and select properties. This will tell you what port this instance is listening on.
0
wayneh13Author Commented:
I made the changes that Shaun Kline suggested -- changing the listening port on the specific instance -- to 1433, and there was no change in behavior.
0
Shaun KlineLead Software EngineerCommented:
Did you verify that no other instance was listening on port 1433? Generally, the first installed instance will listen on port 1433, and prevent any other instance from communicating on that port. A better solution than changing your instance to 1433 is to change your firewall to allow SQL communication via the port that was initially assigned to your instance. From the information I've gleaned from networking people I know, you can limit any security issues by limiting the SQL traffic to your SQL box using the IP address of your web server (and of course the port number).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wayneh13Author Commented:
I don't know that this solution works in my situation as the client had me alter the code for my site so that I no longer need to access the SQL server this way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.