How to attach/deattach a Express database to a fullblown SQL Server 2005

Hi. I would like to if its possible to take a Express ".mdf" file and attach it to a full blown SQL Server 2005?, to "attach" it to a real SQL Server 2005?. And how do i do this?. or do i have to do it since there is a "DataDirectory" directive that manage this for me in the connectionstring?, im a little newbie about this :)

You have two options both are covered in the thread below, if you have more questions post again. Hope this helps

http://forums.asp.net/1194348/ShowPost.aspx

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to

space constraints I have to move one of the database(mdf & ldf) to

second server and access the same from the 1st server.

The following are the steps I ned to follow on the 1st server:

Detached the database

Moved the mdf & ldf files to 2nd machine/server

Attach the database pointing to the 2nd server.

The 1st and 2nd steps went fine, the problem is with the 3rd step.

In the Attach Databases window, I clicked on ADD, it opened LOCATE

DATABASE FILE, the folder structure of the 1st server. Is there any way

I can change the SELECTED PATH and poin to the 2nd server?

I did even mapped network drive to the 2nd server but the mapped drive

is not getting displayed in the LOCATE DATABASE FILE folder structure.

Can we attach mdf/ldf files which reside on other machine?

Thanks in advance.

No - you can't use mdf, ldf or ndf files on devices which are not directly attached to SQL Server. It can't resolve shares, drive mappings, or redirects at this low a level.

Buck Woody

|||Network databases are normally not recommended. Unless your network storage meets strict I/O requirements, it's _not_ supported.

That's said, you can mount a network database if you enable trace flag 1807.

http://support.microsoft.com/kb/304261
http://msdn2.microsoft.com/en-us/library/ms176061.aspx
http://www.microsoft.com/sql/alwayson/default.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that (trust me). See
http://support.microsoft.com/?id=304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ureader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
> FILE, the folder structure of the 1st server. Is there any way I can change
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that
(trust me). See
http://support.microsoft.com/?id=304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ur
eader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABAS
E
> FILE, the folder structure of the 1st server. Is there any way I can chang
e
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach an .MDF file to another server?

Hi all

I am having trouble moving an .mdf file from a dev machine to a production machine.

On the dev machine(windows xp sp2 login <MyName>) I have an SSE instance - part of VisualStudio installation.
on the production machine I have only one full version SS instance called "PROD"(windows server 2k3, login Administrator) I do have SSMS installed too.

I create a website in VS on my dev machine. C:\Websites\Website1
I add to App_Data a new file: DB1.mdf
I add a couple of tables to DB1.mdf and maybe other objects etc. - all dbo.
Then I detach DB1.mdf and physically copy the file (without the .ldf as suggested in books online) via tha LAN.

from: C:\Websites\Website1\App_Data\DB1.mdf(dev machine)
to: C:\DB1.mdf (production machine)

I then try to attach C:\DB1.mdf to PROD and I get an error. The server is looking for a db by the name of the original path on the dev machine C:\Websites\Website1\App_Data\DB1.mdf There seems to be no way to rename the db.-not in VS before detaching it, not on PROD in the GUI to attach.

Just for kicks, I created the same folders on the production machine, so the file would have the same physical path as its name(which I still cannot change). C:\Websites\Website1\App_Data\DB1.mdf I even pasted the original .ldf file. This time it worked. I was able to attach it.

What is up with this? Is an .mdf file for ever stuck with the name of the path where it was first created?

This seems to be a most common scenario, even though I am not deploying a whole website.

Are we always supposed to execute our own script?

How are you reattaching the file ?

You should be able to use the command:

CREATE DATABASE database_name
ON
(name = logical_file_name,
filename = 'new file location')
FOR ATTACH

You'll need to know the logical_file_name that the mdf file uses. Find that out by running this command in the database before detaching:

select name from sysfiles where filename like '%.mdf'
|||

If you right click on the databases folder in Object Explorer in SSMS and select Attach... you get a UI to locate the .mdf file. The UI looks for the other files in database (.ndf or .ldf) in the locations specified in the .mdf file. If it doesn't find them there, it displays an error message for the files it can't find.

You can change the location where it is looking for the other files by editing the "Current File Path" in the lower grid. You can also remove the .ldf files from the grid (so it won't try to attach them) by selecting them in the grid and clicking on the Remove button.

Hope this helps,

Steve

|||

Thanks to both of you.

Both ways work now. I can't even duplicate the issue, and I remember I struggled for a while prior to posting a few days ago. It just didn't like that file name. I swear there is a voodoo ghost on the network!

Steven, I also played with some test UDFs and TVFs written in c#. I right-click deployed the assembly in visual studio. The test was to see what all I needed to do when I reattach the .mdf file. Well the functions work on the new machine just like that. So where is the .net assembly? Is the .dll embedded in the .mdf file? Because that is all I transferred.

Carl

How to attach a MS SQL 2005 database (.mdf) file to MS SQL 2000

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

Quote:

Originally Posted by kentwong

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

with enterprise tools u can attach the datbase|||You have posted this question in the Articles section. I am moving it to the SQL Server forum.

ADMIN

how to attach a .mdf file?

You also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>
Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>

how to attach a .mdf file?

Hi,
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>> You also need the ldf file. Are you clicking the Add button in the att
>> dialog box to browse for the file you want to attach?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>>
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>sql

how to attach a .mdf file?

Hi,
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/d...>
userinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>

How to add a new .mdf with ONLY sql authentication in SQL Express?

Hi all.

I am going around in circles all day on this.

I have a clean new install of win XP SP2, VS 2005 Pro RTM,
and a NAMED instance of Sql Express with mixed authentication mode specified at setup.
The sa pw is 123456

I would have liked just SQL authentication but no such option.

I am trying to add a new mdf file. If I just use the "Add New Item" in VS web app project it just adds a new .mdf file with windows authentication tied to the current profile I am logged in the os with. I am trying to avoid that as this projects is to be zipped and downloaded as a sample app. I am also trying to avoid embedding an instance of SSE as the recipients of the zip file already have SSE running iwth thei VS IDE.

So most logical is to create the file with SQL authentication.
I have to do it by the Add Connection GUI

and for data source: "Microsoft SQL Server Database File (SqlClient) "

for data file name: C:\WebSites\Website1\App_Data\MyDatabase1.mdf (not yet existing file)

Use SQl server authentication:

user: sa, password: 123456 (the ones specified at set up of the SSE)

I keep getting

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

I keep getting the same error no matter what username I use or password, the current login, etc. It just does not let me use sql authentication.

Please help anyone. This is a nightmare.

"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."

This seems that you didn′t activate Mmixed Authentication, its an error you are getting if you only specified the Windows Authentication. Try setting the authentication mode for the express instance:

<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>

HTH, Jens Suessmeyer.

|||

Thanks Jens,

1. I made sure during the install I selected mixed mode.

2. This article essentially says the same. However, I cannot find in the registry any "LoginMode" subkey for either the default SQLEXPRESS, or MySSE named instance.

Actually I did a complete search and there is no Key, Value or Data that contains "LoginMode" in the whole registry.

|||

You can interrogate the database engine with the CTP SQL Express Mamnagement Suite downloadable from Microsoft.

Click select the database engine..... right click and select export

You'll get a file like this:

<?xml version="1.0" encoding="utf-8"?>
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<ServerType id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
<Server name="Shhhh\sqlexpress" description="Local instance - 'bliss\sqlexpress'">
<ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>Shhhh\sqlexpress</ServerName>
<AuthenticationType>0</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions />
</ConnectionInformation>
</Server>
</ServerType>
</Export>

You'll see that ny authentication type is 0 which is windows authentification.

I ust went through this and there is a way to attach this programattically.

Use the Sql Management Classes in your code....

Create an XP UserGroup and user using the SMO classes. Then create the same user group in and user in the database and Attach it. It never fails and yes... I know how frustrating this is. I lost a lost of sleep over this this weekend.

Check out the thread in the first forum of this board for more information.

Renee

|||

Thanks Rene,

I learned something. I don't need the SSMSE becuase I have the full version installed too.

However I still can't fix the problem. This is what I have done so far:

I exported a .regservr and the xml looks just like you show. authetication 0. So I went back and looked in the registry again. I found a LoginMode subkey but in a different key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer

It didn't quite make sense, because I am trying to change a NAMED instance. As expected it did not work. The exported .regsrvr still shows the same authentication mode 0.

I am scared to start adding registry keys that do not even exist.

The nightmare continues.

|||

Carl,

You are doing this programmatically?

|||

I meant to ask you about a link to the thread you mentioned.

What do you mean programmatically?

Create a c# app that could access and modify these registry keys in code? in which case NO.|||

Not true.... I spent the weekend working on this and a friend really helped my understand. You can do excellent work with SSE.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=223272&SiteID=1&PageID=0

post an email adress and I will send you the solution.

|||

http://msdn2.microsoft.com/microsoft.sqlserver.management.smo.server.attachdatabase.aspx

http://msdn2.microsoft.com/ms160723.aspx

|||

oh yeah. . . you can't do SQL authentication only.

its either windows or mixed mode