Using SQL Express 2005 over the network

Using SQL Express 2005 over the network

• Chu Xu

  Hi Wayne,

  I'll answer your questions and a few you didn't ask.

  • You can not attache a file over a UNC path.
  • You configure users using either SSMSE or T-SQL statements along the lines of CREATE USER and CREATE LOGIN. (Every user has to be associated with a login)

  The question you didn't ask is: Can I use User Instances when I'm trying to connect to a remote database The answer is no, User Instances are supported only when working with local databases.

  When you embed a database into a VS application you are basically declaring that this is a single user application where the application, database and SQL Express server will all be on the same computer. The connection string that is created for you is all about local databases. Based on the information you've provided this is not what you're doing, you are trying to create a multi-user application. In multi-user applications you can't attach the database when the application launches, you have to have a server on the network where the database is already attached, and then connect your application to the existing database when you lauch the application.

  I don't claim to be the uber-wizard for writing data-bound multi-user applications in VS, so I'll suggest that you jump into the .NET Framework Data Access and Storage forum to learn from the experts there about the various ways to connect your application to a database.

  Here is the summary:

  • Your connection string is failing becuase you can not attach to a UNC path &
  • You are trying to use User Instance in a remote connection

  Neither of these is supported. Hope this helps.

  Regards,

  Mike Wachal
  SQL Express team

  
  
  

• sven123

  Hi Mike,

  Thanks for the advance. I'll jump over there then and see what happens..

  At least I feel like I'm making progress, rather than banging my head against a brick wall.....

  Thanks

  

  Wayne

  
  

• Meleshko Vasily

  Have a look at this SQL Express blog.

  I have yet to use remote connections. But it basically is to enable TCP/IP protocol and allow remote connections.

  
  
  

• 0xDEADBEEF

  OK. Let me go into a little more detail.

  I have my development PC running SQL Express 2005. I have temporary disabled the f/w, this is to ensure this is causing any issues.

  I have enabled TCP/IP only and the browser service, I pretty sure that my application is talking to SQL Server but it's rejecting access.

  I have wrote my application in VB.NET Express 2005. On my dev PC (k1) all works OK. The database is attached, using the following connection string which has been generated by VB.NET Express 2005.

  <add name="SDS.My.MySettings.SDSConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SDS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

  I have then setup a Vitual PC Image running WinXP. Copied over my application and the applications configuration file and amended the connection to the following.

  <add name="SDS.My.MySettings.SDSConnectionString" connectionString="Data Source=K1\SQLEXPRESS;AttachDbFilename='\\k1\SDS\SDS.mdf';Integrated Security=SSPI;Connect Timeout=30;User Instance=True"

  When I run the application, my error trap reports the following error.

  "User does not have permission to peform this action.", err code 5

  My questions are:

  Can you access the attached file over a UNC share

  How do you configure users and what rights do you need to assign

  Thanks for your help. If you require any more information please let me know.

  Thanks

  Wayne

  
  
• Using SQL Express 2005 over the network