Having all kinds of problems trying to establish a connection between a VB5 application and a remote server Sql Express database. The connection works fine with a user instance on my local machine. At one time I did have the remote connection working successfully using Sql Server authentication, whereby the application was passing the login and password through the connection string as so:
Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=<database name>;Uid=<userid>;Pwd=<password>;
I've been away from using this development database for several weeks. Things are little foggy from that initial effort, and I recently tried putting up a newer copy of the development database on this server from my local machine copy. That local database copy apparently didn't have authentication login/user used by the app, so I created the Login account and assigned the default database, then created the User account, referenced it to the Login account, and gave the user account a role as db_owner. Now when my app attempts to connect with aforementioned connect string, I get an error "Login failed for '< userid>'. I've redone this thing a hundred times and I still get this error. I did put a Windows Update on this server recently so don't know if that's an issue.
I could try using Windows authentication mode, but am not sure how to establish Logins/Users under this mode. Mainly, I'm don't know what the name of these accounts should be. Do you set the Windows login name up in both Login and User accounts? Below is a modified connect string I'd probably use for this mode:
Provider=SQLNCLI.1;Persist Security Info=False;Integrated Security=SSPI;Data Source=<servername>\sqlexpress;Database=<database name>;
Any ideas or suggestion on things to try on either of these modes? At my wits on this and am up against the clock.
TIA ... Rick
If you're using SQL Authentication, the error would suggest that the UserID you're passing in your connection string either doesn't exist as a Login on the Server or that the Login does exist, but it isn't associated with a specific database User. You could probably find out the exact cause by looking in the error log, where it will list not only the error message, but the specific State. If you're getting the 18456 login error, the meaning of the common states can be found in this blog entry.
If you haven't read the Books Online topics covering security already, you should stop what your doing and go read them. No explaination provided in this forum is a subsitute for an actual understanding of the technology.
Regardless of the type of authentication you use, you need both a Login and a User in order to do anything in a database. The Login is what gives you access to the Server while the User is what gives you permission in the database. In Windows Auth, the Login is always associated with either a Machine or Domain User or Group, while SQL Auth Logins can just be any arbitrary name you want to give it. The Database user can be named pretty much anything you want to call it, you make the association between a Login and a User either using Management Studio UI or T-SQL commands. Here is a Windows Auth example:
Login - Domain\Rick (A Windows domain user)
Database User - Rick
You would associate the Domain\Rick Login with the Database User named 'Rick'. You would assign specific permissions in your database to the user named Rick, not to the Login. To be clear, I could have named the user Mike and it wouldn't make a difference. The Database User is totally separate from the Login and from the Windows Users in your domain. This is all explained in Books Online.
Mike
|||Hey Mike,
 Mike Wachal - MSFT wrote: |
|
If you're using SQL Authentication, the error would suggest that the UserID you're passing in your connection string either doesn't exist as a Login on the Server or that the Login does exist, but it isn't associated with a specific database User. |
Neither of these is true. The Login account exist, default database selected as application database, password set, User account created that points to Login account of the same name, role given as db_owner. It should work. It does not.
|
You could probably find out the exact cause by looking in the error log, where it will list not only the error message, but the specific State. If you're getting the 18456 login error, the meaning of the common states can be found in this blog entry.[ |
It goes back and forth haphazardly between state 16 and state 8. Now it's consistently state 16 with a "cannot open database" error. What other permissions are there to give to this thing?
In another connectivity test, there was another oddity. Using Crystal Reports XI, I attempted to create a connection using OLE DB ADO. Selected "Provider for Sql Server" (note: couldn't get a connection out of their Sql Native Client option), logged into the database as SA. However, the application tables belonging to the database were not visible. Everything should be visble and available to SA, right?
Here's what I've been doing to move a copy of the local database up to a development test server, using SqlExpress Manager:
- Detach local db
- Detatch server db and deleted existing MDF/LDF pair since I'm going to overwrite these.
- Copy MDF/LDF pair up to server
- Attach pair to server
- Rename database, removing full pathname from database name.
- Create Login account used by app as Sql Server authentication and establish default db for app. Note, this login seems to persist each time I perform this sequence. Doesn't seem to make a difference if I use what's there, or delete it and start fresh.
- Create User account of same name and point Login Account. Assign role as db_owner.
- Reattach app database on local machine to it's available there again.
This should work, and I had it working once before. Windows authentication mode isn't allowing a connection either. As it is, I've spent a few days on this, am frustrated, and about ready to give up on SqlExpress and move on MySql.
Supplement: I just uninstalled and reinstalled SqlExpress and Sql Native Client on the server. I didn't remove any of the extra tools and such. After successful installation I was going to reconfigure for remote access, however, all my settings were still configure for remote access. I restarted the service(s), including Sql Browser. Ran the process of moving a copy of the database over to server (again), attached, and ... same old error ... "cannot open database", both with Sql and Windows authentication.
Rick
|||Mike,
Well, after three days of troubleshooting, the problem turned out to be that the database name in the connection string must explicitly reference the .MDF extension. For example...
Problematic connect string:
connection=Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=myDatabase
Working connection string
connection=Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=myDatabase.mdf
To the best of my recollection, Sql Server doesn't normally require an extension, but apparently Sql Express now does.
Rick|||
SQL Express does not require the extention, but it does require the correct name. SQL Server will also keep a hold of the name of a database and tie it to a specific file location, even after detach. Database names can be pretty much anything, including a full path to a file, in fact, when you use AttachDbFilename and don't specificy an explicit name, your database will actually be given an "auto name" that is the path to the file.
It appears that at some point in the past, the file managed to get named as the file name with extenstion rather than just the base file without extention. Since that informaiton is cached in some form in the Master database, the next time you attached a database from the same path, it "picks up" to name from the previous attach. This isn't unique to SQL Express, any copy of SQL Server could do this.
If you look in Management Studio at the server, you should find the database name listed as myDatabase.mdf. You should be able to change this if you want by just renaming the database.
Mike
|||Yeah, I thought about it some more overnight. When moving the local copy up to server and renaming the file (removing the full path from the name), I didn't remove the .MDF extension. So, just have to be mindful of this in future when attaching any database to the server.
