Trying to connect to MDF Database without using a trusted connection

Question

Hi,

Is there anyway to connect to an MDF file without using a trusted logon?

I've gone into management studio and created an account called bob with password bob, given that account full access and also added the account to the database. When i try to connect using that account I get the following error

Login in failed for user 'bob'. The user is not associated with a trusted SQL server connection.

Here is my connection string

<add name="testdata" 
             connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\cidata.mdf;Connect Timeout=30;User Instance=True;User Id=bob;Password=bob"
             providerName="System.Data.SqlClient" />

Are there any limitations with MDF? I'm trying to get around the 200MB database limit given to me by godaddy so any other recomendations for a database (except access) would be welcome.

Thank you

Answer 1

Do you have sql server express configured to use mixed mode authentication instead of windows authentication? You can check and change if needed as follows:

With MS SQL Server management studio go to the sql server instance and right click and choose server properties. then go to Security and check the authentication mode under server authentication and set it to "mixed mode". You will need to restart the instance for this to work.

If you don't have sql server management studio you can use the following query from for example osql:

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

Answer 2

It seems godaddy doesn't allow MDF files to be attached dynamically. I suppose they try to stop people going past the allowed 200MB limit.

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q%5F23083868.html