Attaching MDF with AttachDBFilename?

I am attempting to attach an .mdf to SQL Express using "AttachDBFilename" in
the connection string, and I have a few questions on the issue.
I am planning on setting this up on a network with both the .mdf and the
instance of SQL Server residing on a server. All the clients will be runnin
g
an app that will have access to the SQL Server instance and the fileshare,
and they will attempt to use a connection string with the "AttachDBFilename"
.
I have successfully connected to the instance of SQL Server and have access
to the .mdf from a client, but I have not been able to get the connection to
attach the .mdf file. If I run the app on the server it works, but on a
client I get an error saying something like "The file '..' is on a network
path that is not supported for database files."
My questions are:
Can this be done using "AttachDBFilename"?
If yes, any suggestion on what I may be missing?
Even if I do get this method to work will it support multiple users?
Thanks,
JimThe database file (mdf) only needs to be attached one time -at the server.
Clients only need to connect to the server and have access to the database.
If SQL Express was installed on each client, and those clients used the
'AttachDBFilename', they would be attempting to attach a database file to
their local copy of SQL Express. Database files cannot be attached from a
unc network path. They would each need a local copy of the database for that
to work. However, it sounds like you want a centrally located database, not
individual copies.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:5732D62A-EE09-4ED6-8548-FFB7AC58EC2E@.microsoft.com...
>I am attempting to attach an .mdf to SQL Express using "AttachDBFilename"
>in
> the connection string, and I have a few questions on the issue.
> I am planning on setting this up on a network with both the .mdf and the
> instance of SQL Server residing on a server. All the clients will be
> running
> an app that will have access to the SQL Server instance and the fileshare,
> and they will attempt to use a connection string with the
> "AttachDBFilename".
> I have successfully connected to the instance of SQL Server and have
> access
> to the .mdf from a client, but I have not been able to get the connection
> to
> attach the .mdf file. If I run the app on the server it works, but on a
> client I get an error saying something like "The file '..' is on a network
> path that is not supported for database files."
> My questions are:
> Can this be done using "AttachDBFilename"?
> If yes, any suggestion on what I may be missing?
> Even if I do get this method to work will it support multiple users?
> --
> Thanks,
> Jim

AttachDbFilename, |DataDirectory| and SQL Server 2005 June CTP Relative Paths

Hello,

I am using "AttachDbFilename" to connect to a June CTP SQLExpress database. I have a windows forms application that has the following connection string (which works fine at the moment):

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|test.mdf';Integrated Security=True;User Instance=True"

However, I would like to make my connection string:

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|..\..\Database\test.mdf';Integrated Security=True;User Instance=True"

But no matter what combination I try (including not using DataDirectory at all), I cannot seem to open the database using the relative path.

Can someone please assist/clarify?

Thanks,
Sean H.

Hi,

DataDirectory is specifically restricted to only allow access to files in the directory pointed to by the DataDirectory value or any of its subdirectories, but not directories up in the chain.

This is to prevent security issues such as those common in older web servers where you could navigate the server file-system by doing something like http://www.mywebsite.com/..\..\..\etc\passwords

-pablo|||When you say "the directory pointed to by the DataDirectory value " what exactly is that? In other words, what is this value and where can I set/change it?

Thanks - Daniel|||

you can change:

AppDomain.CurrentDomain.setData(“DataDirectory”,”C:\newPath\”);

AttachDbFilename, |DataDirectory| and SQL Server 2005 June CTP Relative Paths

Hello,

I am using "AttachDbFilename" to connect to a June CTP SQLExpress database. I have a windows forms application that has the following connection string (which works fine at the moment):

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|test.mdf';Integrated Security=True;User Instance=True"

However, I would like to make my connection string:

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|..\..\Database\test.mdf';Integrated Security=True;User Instance=True"

But no matter what combination I try (including not using DataDirectory at all), I cannot seem to open the database using the relative path.

Can someone please assist/clarify?

Thanks,
Sean H.

Hi,

DataDirectory is specifically restricted to only allow access to files in the directory pointed to by the DataDirectory value or any of its subdirectories, but not directories up in the chain.

This is to prevent security issues such as those common in older web servers where you could navigate the server file-system by doing something like http://www.mywebsite.com/..\..\..\etc\passwords

-pablo|||When you say "the directory pointed to by the DataDirectory value " what exactly is that? In other words, what is this value and where can I set/change it?

Thanks - Daniel|||

you can change:

AppDomain.CurrentDomain.setData(“DataDirectory”,”C:\newPath\”);

AttachDbFilename, |DataDirectory| and SQL Server 2005 June CTP Relative Paths

Hello,

I am using "AttachDbFilename" to connect to a June CTP SQLExpress database. I have a windows forms application that has the following connection string (which works fine at the moment):

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|test.mdf';Integrated Security=True;User Instance=True"

However, I would like to make my connection string:

"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|..\..\Database\test.mdf';Integrated Security=True;User Instance=True"

But no matter what combination I try (including not using DataDirectory at all), I cannot seem to open the database using the relative path.

Can someone please assist/clarify?

Thanks,
Sean H.

Hi,

DataDirectory is specifically restricted to only allow access to files in the directory pointed to by the DataDirectory value or any of its subdirectories, but not directories up in the chain.

This is to prevent security issues such as those common in older web servers where you could navigate the server file-system by doing something like http://www.mywebsite.com/..\..\..\etc\passwords

-pablo|||When you say "the directory pointed to by the DataDirectory value " what exactly is that? In other words, what is this value and where can I set/change it?

Thanks - Daniel|||

you can change:

AppDomain.CurrentDomain.setData(“DataDirectory”,”C:\newPath\”);

AttachDBFileName refers to original log file

Hello,

I'm using the AttachDBFilename option in the SQL connection string to attach
a database mdf file copied to another machine.

There is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:

Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Database\Strategic.TimeMaster.Database.SQL2005\TimeMasterDat_log.LDF" may be incorrect

How can I get the attach to avoid referring to this log file?

Coudl you please post your connection string here ? Seems that you are mixing up the setting for server / user instances.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Here it is:

Data Source=.\SQLExpress;AttachDBFileName=C:\Program Files\Time Master\TimeMasterDat.mdf;Initial Catalog=TimeMaster;Trusted_Connection=yes;

The mdf file should be attached to a local instance of SQLExpress. The mdf has been copied from my development machine and the it seems to think that this mdf file is refering to this log file.

It shouldn't, it should create a log file on the local machine.

|||Try to add the "User Instance=true;" to the connectionstring.|||

That worked, but creating another User Instance was not really what I was looking to do, but will probably do

Thanks

|||Did you want to attach the database to the server for a server instance ? or did you want to use the user instance feature of SQL Express ? If you want to use the user instance you WILL have to create one instance per database.

-Jens.|||A server instance I suppose. The server and client being on the same machine.|||

If you want to use a server attached instance you will have to attach the database first (if you have no log LDF file you will have to try to attach the database using sp_attach_single_file_db) using sp_attachdb or the equivalent GUI action in the managment studio.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

http://msdn2.microsoft.com/en-us/library/ms130822.aspx

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John

AttachDBFileName option in Connection String

I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?

You might look following link. you need to provide the database name along with the DB filename

http://msdn2.microsoft.com/en-us/library/ms130822.aspx

|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?

Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||

Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.

Refer this link it will help u

Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu|||

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

Hope that cleared things up for you,

John

AttachDbFilename in SQL Server (not express)

I've been working with VWD Express since its Beta 1, then Beta 2 and finally the final release.
With the Beta 2 I've been worked with crystalreports without any problem in connections to SQL Server 2000.
The problem arrives now: I'm working with godaddy.com (good guys)
They tell me to connect to my databases (SQLServer 2005) with this:
<connectionStrings>
<add name="Personal" connectionString="
Server=whsql-v04.prod.mesa1.secureserver.net;
Database=DB_675;
User ID=user_id;
Password=password;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="
Server=whsql-v04.prod.mesa1.secureserver.net;
Database=DB_675;
User ID=user_id;
Password=password;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
</connectionStrings>

But I've been always using this:
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|myMDF.mdf;
Integrated Security=True;
User Instance=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>

Does it means that I cannot use "AttachDbFilename"? I mean, I can't use an attached db file?
Should I create all my tables another time? (they use a Web database Admin)
Thanks in advance.
Correct. SQL Express does not scale in a shared hosting environment. See this post:http://forums.asp.net/1108464/ShowPost.aspx

AttachDbFilename in DbNameDataSet.xsd

How can I use AttachDbFilename in DbNameDataSet.xsd to access the database in the same directory as the exe file (i.e. the application using it)?Use Application.StartupPath to get the directory your program is in.
To get the complete filename to your mdf file, use:

string filename = System.IO.Path.Combine(Application.StartupPath, "filename.mdf");

|||Thank you but my mistake. I failed to mention it is C++ program. What you wrote appears to be VB. Tho I will add it to my VB code snippets.|||

Actually, it's C#. However, it should work with any .NET language.

AttachDBFileName - refers to previous log file

Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Databas e\Strategic.TimeMaster.Database.SQL2005\TimeMaster Dat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?
Answered in
http://forums.microsoft.com/forums/ShowPost.aspx?PostID=1081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

AttachDBFileName - refers to previous log file

Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMast
er\Database\Strategic.TimeMaster.Dat
abase.SQL2005\TimeMasterDat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?Answered in
http://forums.microsoft.com/forums/...081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

AttachDBfilename

I was getting SQL Server errors trying to run the Time Tracker kit, soI tried to establish a connection with the timetracker.mdf from the
VWD Database Explorer - but no luck:

Data Source: Microsoft SQL Server Database File (SqlClient)

Database File name: Visual Studio2005\WebSites\TimeTracker1\App_Data\TimeTracker.mdf

Using either Windows or SQLServer authentication, they both fail when I test the connection.

Any ideas ? I gave myself and the ASPNET account every permission possible in SQLServer.Create a connection from a datasource control to test it or add a connection in VWD in server explorer ( i hope they have that hehe I use VS.NET 2005 pro ). Test the connection until it works. I had to play with it until I got it to work but I had the same problem. Make sure you are saying that you want a user instantce. First make a connection to your db and make sure that works. Then try to attach with the db.

AttachDBFileName

Group,
Does the AttachDBFileName requires a running SQL Server Express (or any
edition) on that server?
Meaning, can it be accessed like MS Access, not necessarily need a software
installed on that computer, though using OLEDB or connecting with ODBC,
DSN-less still allow you to connect to that database.
Please help.
Even Access requires that software be installed on the computer.
AttachDBFileName points to an attachable SQLS database file in a
SqlClient connection string. Here's some links to the relevant
documentation:
http://msdn2.microsoft.com/en-us/library/ms254499.aspx
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.a ttachdbfilename.aspx[/url]
--Mary
On Fri, 16 Feb 2007 09:50:32 -0800, Ronin
<Ronin@.discussions.microsoft.com> wrote:

>Group,
>Does the AttachDBFileName requires a running SQL Server Express (or any
>edition) on that server?
>Meaning, can it be accessed like MS Access, not necessarily need a software
>installed on that computer, though using OLEDB or connecting with ODBC,
>DSN-less still allow you to connect to that database.
>
>Please help.
|||The software doesn't necessarily have to be on "that" computer. SQL Server
needs to be running on some machine to which your PC or program has access.
You can't use a database file without first attaching it to an installed
instance of SQL Server and the database file must be local to the server -
on direct-attached or SAN storage but not on a NAS or network drive.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

AttachDBFileName

Group,
Does the AttachDBFileName requires a running SQL Server Express (or any
edition) on that server?
Meaning, can it be accessed like MS Access, not necessarily need a software
installed on that computer, though using OLEDB or connecting with ODBC,
DSN-less still allow you to connect to that database.
Please help.Even Access requires that software be installed on the computer.
AttachDBFileName points to an attachable SQLS database file in a
SqlClient connection string. Here's some links to the relevant
documentation:
http://msdn2.microsoft.com/en-us/library/ms254499.aspx
http://msdn2.microsoft.com/en-us/li...tionstring.aspx
http://msdn2.microsoft.com/en-us/li...dbfilename.aspx
--Mary
On Fri, 16 Feb 2007 09:50:32 -0800, Ronin
<Ronin@.discussions.microsoft.com> wrote:

>Group,
>Does the AttachDBFileName requires a running SQL Server Express (or any
>edition) on that server?
>Meaning, can it be accessed like MS Access, not necessarily need a software
>installed on that computer, though using OLEDB or connecting with ODBC,
>DSN-less still allow you to connect to that database.
>
>Please help.|||The software doesn't necessarily have to be on "that" computer. SQL Server
needs to be running on some machine to which your PC or program has access.
You can't use a database file without first attaching it to an installed
instance of SQL Server and the database file must be local to the server -
on direct-attached or SAN storage but not on a NAS or network drive.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

AttachDBFileName

Group,
Does the AttachDBFileName requires a running SQL Server Express (or any
edition) on that server?
Meaning, can it be accessed like MS Access, not necessarily need a software
installed on that computer, though using OLEDB or connecting with ODBC,
DSN-less still allow you to connect to that database.
Please help.Even Access requires that software be installed on the computer.
AttachDBFileName points to an attachable SQLS database file in a
SqlClient connection string. Here's some links to the relevant
documentation:
http://msdn2.microsoft.com/en-us/library/ms254499.aspx
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.attachdbfilename.aspx
--Mary
On Fri, 16 Feb 2007 09:50:32 -0800, Ronin
<Ronin@.discussions.microsoft.com> wrote:
>Group,
>Does the AttachDBFileName requires a running SQL Server Express (or any
>edition) on that server?
>Meaning, can it be accessed like MS Access, not necessarily need a software
>installed on that computer, though using OLEDB or connecting with ODBC,
>DSN-less still allow you to connect to that database.
>
>Please help.|||The software doesn't necessarily have to be on "that" computer. SQL Server
needs to be running on some machine to which your PC or program has access.
You can't use a database file without first attaching it to an installed
instance of SQL Server and the database file must be local to the server -
on direct-attached or SAN storage but not on a NAS or network drive.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--