What setting has to be done to connect with database with this connection string given below?

Question

In my .exe setup having connection string

Data Source=SERVER;Initial Catalog=POS_Chimur;User ID=sa;Integrated security=false

I have to install database for this exe what settings will be needed according to above connectionString.

Till now I have installed sql server with default instance with name of pc SERVER. Still i am unable to connect with above connection string.

Answer 1

You need to cheat your way in. Here's how I would approach this problem:

Data Source=SERVER;

You can create an alias to point to your final instance using "SQL Server Configuration Manager", "Aliases"

Initial Catalog=POS_Chimur;

You need to have a database named POS_Chimur

User ID=sa;Integrated security=false

Here, you need to provide a SQL login named sa with no password. I recommend to rename actual sa account to original_sa then create a new account named sa with no password. You also need to create a user mapping for that new account in the POS_Chimur database using this code.

CREATE USER sa FOR LOGIN sa;
ALTER ROLE [db_owner] ADD MEMBER sa;

If DBO doesn't work then you can give it SysAdmin rights if you still have error.

Answer 2

If you are using SQL Server security, you need to specify a username and a password, like this (where you replace 'mySApassword' with the actual password):

    Server=SERVER;Database=POS_Chimur;User Id=sa;Password=mySApassword;

In the event you want to use Windows security, you will need this connection string:

    Server=SERVER;Database=POS_Chimur;Trusted_Connection=True;

If you are running the executable on the same machine as where SQL Server is running, you can replace 'SERVER' with '.' in order to make it work on all computers, if you need to distribute it to more than one pc.

Here's some more information about SQL Server 2008 connection strings.

Answer 3

I see two things mainly:

1. You are connecting with SQL Server login

   • Go to SQL Server Management studio
   • Connect to the database server with administrative account you know and that works
   • right mouse click on the server in the 'Object Explorer' window
   • choose security
   • In the Server authentication group, choose 'SQL Server and Windows Authentication mode'
   • Restart SQL Server

2. This account is sa and doesn't have a password

   • Go to SQL Server Management studio
   • connect to the database server with administrative account you know and that works
   • unfold the server object [-]
   • unfold the Security folder [-]
   • unfold the Logins folder => find sa login
   • right click on it and click Properties
   • In General section uncheck the Enforce password policy checkbox and clean the passwords in both text boxes
   • In Status section, make sure that Login is Enabled and that the Permissions to connect is set to Grant
   • click Ok
   • confirm, that you want to create a login with blank password (which is obviously always a risk)

After performing those steps, please log out, and try to log in again, but change the Authentication drop down value to 'SQL Server Authentication' and try to login with sa and empty password, if it works, then the connection string should be fine too.

Answer 4

You need to mention the Provider. Your connectionstring should look like this.

Data Source=SERVER;Initial Catalog=POS_Chimur;User ID=sa;Integrated security=false; Provider="System.Data.SqlClient"