Can't connect to sql express 2005

I have an very big application written in vb6 running on msde 2000. I tried to change it to sql express 2005 and I cannot connect to the database. I wrote a sample app in vb6 using adodc and it works fine. From the other app I get no connection. I copied the same connection string!. I do not use the adodc control.I connect with a connection object.I have enabled pipes and tcp/ip. Any ideas or suggestions more than welcome.

Which error do you get ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

There are many KB articles which walk through remote connections to SQL Express. Please check out the following:

http://support.microsoft.com/kb/555585/en-us - in addition to enabling tcp connections, you need to ensure you have allowed remote connections and setup the firewall correctly.

http://support.microsoft.com/kb/319930/en-us

|||

The error message is -2147217843 Login failed for user 'Master'.

I detached the databases from msde2000 and attached them on SQLExpress 2005.

|||I don't have a firewall installed and by inspecting the registry key the LoginMode is set to 2 Mixed Mode authentication.|||Can you post your connection string here? You can hide some sensitive info.|||

The connection is

Server = "CMP\SQLEXPRESS"

g_SQLProvider = "SQLNCLI.1"

ConnOpsDB = "Persist Security Info=False;User ID=George;Password=''" _
& ";Data Source=" & Server
With cnOpsDB
.Provider = g_SQLProvider
.ConnectionString = ConnOpsDB
.Open
.DefaultDatabase = "MercDB"
End With

|||With this connection string, do you still get "Login failed for user 'Master'."? Looks like the user is "george" here. But, anyway you should make sure the user in the connection string should have login premission to its default or initial database.|||

With the above connection string I am getting "Login failed for user 'George'. THe problem is that after attaching the database from MSDE 2000to Sql Express 2005 and using the MS SQL Server Management Studio I cannot set the permissions right. I cannot have the user i want behave as sa. any ideas?

|||OK, you Upgraded / attached the database to the SQL Server 2005 Express instance. The database principal is included within the database whereas the login fo the user you are trying to use is not automatically created as a server principal on the server level. YOu will have to either delete the user in the database and recreate it at server level granting him acces and the appropiate rights in the database from scratch or will have to remap the newly created server principal with the "old" database principal. See more information here:

http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de