• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Can't Connect to SQL 2005 from VB 2005 Express.

Visual Basic 2005 Express:

I was successfully able to create a DataGrid and work with it using MS Access Tables.  I then tried to do the same thing with SQL 2005 tables and I can't connect.  I am able to Link my SQL tables to Access via ODBC and then use the linked table as a datasource in Visual Studio.

My SQL tables are on a different machine.  I have SQL 2005 on my machine, but the actual server instance runs from another box.

Here is what I am doing:

Add new datasource  .. Wizard comes up ... database ....New Connection  ...
Microsoft SQL Server Database File (SqlClient)
browse  .. find CABDATA.mdf ... open
I enter my password , etc.  then test the connection.  It fails blahblahblah may not allow remote connections.

When I look at the connection string generated by the wizard, the source is SQLEXPRESS
 
Data Source=.\SQLEXPRESS;AttachDbFilename=\\BigServer\data\CabData.MDF;Persist Security Info=True;User ID=sa;Connect Timeout=30;User Instance=True

Can I somehow change the connect string to make this work with my SQL 2005?




Microsoft SQL Server Database File (SqlClient)
0
Volibrawl
Asked:
Volibrawl
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
alter the configurations for SQL 2005 to allow remote connections
0
 
YveauCommented:
Let's go through the connection string:

Data Source=.\SQLEXPRESS;
This is pointing to the local SQLEXPRESS instance on the local machine ... what you wanted was:
    Data Source=BigServer;
When that is a named instance (like SQLEXPRESS), then it should have been
    Data Source=BigServer\SQLEXPRESS;

AttachDbFilename=\\BigServer\data\CabData.MDF;
... what is it with connecting database files ??? Just not do that, it's a bad habit ... just connect to the database, not it's files. So replace this with:
    Initial Catalog=CabData;

Persist Security Info=True;
no comment here ...

User ID=sa;
OK, so you are willing to connect using the 'sa' account. Worst thing you can do, but let's forget about that for now. It is possible, but when you do login by means of SQL authentication, also mention the password. So change it to:
    User ID=sa;Password=MyLittleSecret;

Connect Timeout=30;User Instance=True
no comment on these either ...

Bring them all together, and you end up with my advise:
    Data Source=BigServer;Initial Catalog=CabData;User ID=sa;Password=MyLittleSecret;Persist Security Info=True;Connect Timeout=30;User Instance=True;

Hope this helps ...
0
 
VolibrawlAuthor Commented:
Thank you for valuable information, thus the points, however, the problem is not resolved.

Upon further investigation, it would appear that the Express Version of Visual Studio will NOT ALLOW you to connect to SQL 2005, (only SQL Server Express and Access tables).  Unless someone has another opinion on this, I will assume I can't do what I am asking.



0
 
YveauCommented:
Glad I could be of any help ... sorry it didn't work out as I hoped !
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now