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.

Indogermanic

For a local SQL Server (on the same machine as your application), I typically use . or (local) as the "machine name". With this, I'm independent of what the actual machine name really is - it works on any machine – Melanoma

You're question is quite unclear and the title doesn't tell anything about your problem. Please fix it. – Adest

This connection String already Hard coded in set up and i want this set up in another machine named as SERVER – Indogermanic

I got some of this connection string SERVER is in data source means You have to install default instance and PC name should be SERVER. Catlog indicates you should have data base name like POS_Chimur. – Saphra

without password which system can connect i didn't get this.??? – Saphra

can you indicate which error you get? BTW you should use a host name for the datasource or the ip of the machine you try to connect. – Cannabin

Answer 1

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.

Travesty

Answer 2

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.

Gerbil

Answer 3

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.

Suffering

i can't replace single word b'cause it is already hardcoded and published exe – Saphra

i don't have it's source code...to edit i just got connection string in it somehow – Saphra

As far as I can see, you need to be able to specify a password somewhere or... not use a password for the "sa" user. But that second option is basically not done because it's a severe security risk. You could try it for testing the connection string of course, see if that works and then work towards a solution for that. You can also check if the executable is using a configuration file which contains the connection string and change that after you have worked out a fully functional connection string. Otherwise I'm afraid you will need to get that application changed. – Suffering

in this exe there isn't any configuration file thats why i am getting problems. – Saphra

Have you tried clearing the "sa" password in SQL Server? Just as a test I mean. – Suffering

You can use the SQL Server Management Studio as outlined in that article. – Suffering

At this point I would also advice to clear your head and follow the steps in this troubleshooting article in order to verify that everything is set up correctly – Suffering

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"

Bedaub