[RESOLVED] SQL2005 connection string. vb.net\2005Express

[tomroth]

Hi all!

This has been driving me nuts for a few days now of searching forums for answers. I need to connect to my remote (about 5 feet away) Windows Server 2003 SQL Server 2005 Express database. It is using Windows Authentication. I can login just fine to SQL using remote desktop to make any changes to the Management Studio and I have made exceptions for the server and the browser in the firewall. Here is my connection string:

Public strDBLocal As String = "Data Source=DATA_SERVER\SQLEXPRESS;Initial Catalog=dbdbdbdb;User ID=xxxxx;Password=yyyy"

DATA_SERVER is the name of the computer and what I use to login using remote desktop. I have tried using "Integrated Security=SSPI" and some others. My current string seems to get the closest. the error is:

"Login failed for user xxxxx. The user is not associated with a trusted SQL server connection."

What am I doing wrong? How do I make this user associated with a trusted SQL connection? Is this not using Windows Authentication? I know how to change to SQL Server Authentication, but how do I create that user name and password?

Any help would be appreciated!!!

Thanks.

[si_the_geek]

If you want to use Windows Authentication, add the Integrated Security part, and remove the UserID and Password parts (they will be set automatically in a different way).

If you want to use SQL Server Authentication, add a new user in the Security section of Management Studio, and use the connection string you posted.

[tomroth]

That didn't work. I created a new LOGIN under security and gave it a password. My connection string stayed exactly as above except for entering the user id and password that I created and I get the same error that is in red above. Any other suggestions?

[si_the_geek]

Try using the alternative parameter names:

Public strDBLocal As String = "Server=DATA_SERVER\SQLEXPRESS;Database=dbdbdbdb;UID=xxxxx;Pwd=yyyy"

[GaryMazzone]

One of the issues can be that out of the box SQL Express does not allow remote connections. You would cahnge that using the Surface area configuration tool. When you remote in you are running from that remote machine (at least to SQL Server Express) so that wold not be an issue.

[tomroth]

None of the suggestions seemed to work, so I uninstalled SQL and reinstalled putting it in MIXED mode (both Windows and SQL Server authentication) and tried it then with ";User ID=sa;Password=****" where **** is the password I entered for the 'sa' user ID during installation. Works like a champ now. Go figure!