Basic problem

I downloaded SQLExpress and Visual Studio Express to my home computer.

I built a simple database, adding data through theSQLexpress admin tool.

I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder.

I can still see and work in the database through SQL server Express.

The web page and the MSStudio attempt to connect to the mdf file both fail with this message:

Cannot open user default database. Login failed.
Login failed for user 'KAAAK/Administrator'.

So it seems to be trying to connect as the Windows user.

When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user.

from web.config:

<

connectionStrings>

<

addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345"providerName="System.Data.SqlClient"/>

</

connectionStrings>

That was changed from the original string created automatically by MS Studio

<

connectionStrings>

<

addname="stocksConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\stocks.mdf;Integrated Security=True;User Instance=True;"providerName="System.Data.SqlClient"/>

</

connectionStrings>I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.?

Thanks, Michael

Hi!,

This may happen because the SQL server is configure for windows authentication only. Change to mixed mode.

Hope this will help.

Regards

backups OK in MSDE2000 but won't work in SQLexpress

I'm migrating our app from VS2003/MSDE2000 to VS2005/SQLexpress. Generally no
problems, but the backup of database always fails.
The backup command is
"BACKUP DATABASE icespy5 TO DISK='C:\testbackup'"
I get:
"Cannot open backup device 'C:\testbackup'. Operating system error 5(error not
found). BACKUP DATABASE is terminating abnormally."
The same command works fine with the MSDE database. I have tried other
backup destinations but with no success. I have full admin rights; backup
fails in both debug and release environments.
Any ideas what's wrong?
Evidently permissions are not granted! I can back up to the folder where the
..mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||quilkin,
Try changing SQL service to log in as a Local System instead of a Local or Network Service
Jeff
Check Point Software
================================================== ===
You must be using Outlook Express/Windows Mail or some other type of newsgroup reader to
see and download the file attachment(s). If you are not using a reader, follow
the link below to setup Outlook Express. Click on "Open with newsreader"
under the MS Retail Management System on the right.
http://tinyurl.com/75bgz
================================================== ===
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message news:D58DA277-121E-49AE-96B1-7F91C674DD8D@.microsoft.com...
Evidently permissions are not granted! I can back up to the folder where the
.mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>

Backup to Share with SMO fails, but works to local hard disk

I have a short program (vb.net) which backs up an SQLExpress database. It works fine if the destination is a file on the local drive. However, it always fails if the destination file is a network share or a mapped share. The same program verifies the share is available by trying to read and write a small file before performing the backup.

This is a stripped down version of the code

Dim svr As Server = New Server("Servername")
svr.ConnectionContext.LoginSecure = false
svr.ConnectionContext.Login = TextBoxBULogin.Text
svr.ConnectionContext.Password = TextBoxBUPwd.Text
Dim bkp As Backup = New Backup()
bkp.Action = BackupActionType.Database
bkp.Database = TextBoxBUDatabase.Text
Dim x As New Microsoft.SqlServer.Management.Smo.BackupDeviceItem(destfilepath, DeviceType.File)
bkp.Devices.Add(x)
bkp.SqlBackup(svr)

The exception generated is an SMO.FailedOperationException and reads "System.Data.SqlClient.SqlError: Cannot open backup device 'Z:\v1tbcontacts00.bak'. Device error or device off-line."

Where "Z:\" is a network share. If the backup device string is set to "C:\v1tbcontacts00.bak" the backup succeeds.

Have you tried to use Transact-SQL to backup to the share? In any case you may find it better to back up to the local drive, then copy the backup file using operating system commands to the network share for performance purposes.|||

Henry,

using SQL authentication will make SMO to use the SQL Server Sevice account to authenticate on the network share, so although you might be able to check the network share for existance within your application, the credentials used for this is not used for making the backup on the file share.

Client application (Windows token) --> SQL Server using Windows authentication and connected with Windows Authentication --> SMO uses the Windows credentials of the connected client to access the share

Client application (Windows token) --> SQL Server using Mixed Authentication and connected with SQL Server Authentication --> SMO uses the SQL Server Service account credentials of to access the share

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The practical solution to my issue is to perform a database backup to the local hard disk and then copy the backup to a share.

Better still. the nugget of information about how authentication works with SMO explains why the problem occured in the first place.

Thanks