How to connect to sql server without any password

Question

I have just built an app with database using sql server 2008 and visual C# 2010 ultimate, I have included sql server 2008 on my installation files then install it to a different pc. but when I try to open it on different pc i got this message.

i use integrated security set as true on my connection string. do you have any idea to let the user open the database without login to the database?

Answer 1

The Windows account name in the error message looks like you have a local account instead of a domain account.  In order to use Windows authentication, both computers need to be in a domain and you need to login as a domain account with access to the database.  You may find it easier to use SQL authentication if you have no domain in your environment.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

So you mean that I need to change my code and my configuration on my SQL Server about how I connect to the database?? This is a piece of my code to connect to my database

stringkoneksi = @"Server = .\SQLEXPRESS;" +
                            "Database = UGsimplify;" +
                            "Integrated Security = true";

then what part that should be change in order to make it connectible on other computer?

Answer 3

stringkoneksi = @"Server = .\SQLEXPRESS;" +
                            "Database = UGsimplify;" +
                            "Integrated Security = true";

then what part that should be change in order to make it connectible on other computer?

To connect to a remote server using Windows authentication, simply replace "Server=.\SQLEXPRESS" with "Server=RemoteServerName\SQLEXPRESS".  This will connect to the remote server using the Windows account of the current process.  In order for this to work, the Windows account needs to be a domain account.  This implies both computers are members of a Windows domain.

The Windows account will need access to the UGsimplify database.  You can add the login and user by running the query below on the remote database:

USE UGsimplify;
CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS;
CREATE USER [MyDomain\MyAccount];

Additionally, the user will need permissions on objects accessed by the application.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

It means that the other computer need to be in one network right? But what about they are not connected or they aren't a members of a Windows domain, I mean this app must be able to open the database when I installed it purely on a single computer that is not in a network? Should I use Sql Authentication, and how I can do it?

Answer 5

Hello Raditya,

When the computer where the SQL Server is installed and the computer from where you are accessing the SQL Server are not belonging to the same Windows domain, you have only one solution that you have given in your last question : to use the SQL Server authentification .

You must  first enable the use of the SQL Server authentification :

- on the computer where the SQL Server is installed, you connect to the SQL Server with SQL Server Management Studio (SSMS).In the right panal, you right-click on the name of the instance and in the conceptual menu, you click on Properties.You arrive in a new form with a label like "Properties of the server ComputerName\InstanceName" where ComputerName is the name of the computer where SQL Server is installed and InstanceName is the "short" name of the SQL Server instance ( if you have a SQL Server Express, InstanceName  has SQLEXPRESS as default name)

- in the right panel, click on the Security item.

I am sorry , but i have only a french SQL Server but the french and other editions have the same display.

Don't forget to validate by clicking on the OK button ( Maybe, the SSMS will complain that the sa login is disabled )

You must avoid to use the sa SQL Server login which is existing by default ( this login has all the permissions ). You must create a SQL Server login specific to your application with reduced permissions ( minimum is public as server role and on the database level : db_datareader and db_datawriter , if you don't give them , you will disallow the use of SELECT/INSERT/UPDATE/DELETE on your database ). For that, doubleclick on your SQL Server instance name in SSMS.Double-click on the Security node, afterwards, double-click on the Connections node ( 1st node ) and right-click on the Connections node, click on the New Connection item and create your new SQL Server login

I have filled the most important fields ( especially the "Base de données par defaut"  field = database used for a default connection ) 

I have only developped the Dan's idea which is given in his 1st reply.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

It means that the other computer need to be in one network right? But what about they are not connected or they aren't a members of a Windows domain, I mean this app must be able to open the database when I installed it purely on a single computer that is not in a network? Should I use Sql Authentication, and how I can do it?

Yes, to connect to a database using Windows authentication, both the database and application need to run on the same computer or both computers must belong to a trusted domain.   With the local database, SQL Server honors the local Windows credentials of the currently logged in user.  To connect to a remote database with Windows authentication, the user must login using a domain account.  This implies both computers are members of the same (or trusted) domain. 

Alternatively, you can specify a SQL login user id and password in the connection string instead of "Integrated Security=SSPI" so that user SQL authentication instead of Windows authentication.  SQL authentication will work in both local and remote scenarios as long as SQL authentication is allowed (see Papy's response). The script below shows how to create the SQL login, user and object permissions for this purpose.  You can also use the SSMS GUI for this task.

USE UGsimplify;
CREATE LOGIN MySqlLogin WITH PASSWORD = 'MyP@ssW0Rd', DEFAULT_DATABASE = UGsimplify;
CREATE USER MySqlLogin;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON DATABASE::UGsimplify TO MySqlLogin;

Note that the connection string in your application should be configurable anyway so it is a trivial task to use either Windows or SQL authentication depending on the installation scenario.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

It means that the other computer need to be in one network right? But what about they are not connected or they aren't a members of a Windows domain, I mean this app must be able to open the database when I installed it purely on a single computer that is not in a network? Should I use Sql Authentication, and how I can do it?

Yes, to connect to a database using Windows authentication, both the database and application need to run on the same computer or both computers must belong to a trusted domain.   With the local database, SQL Server honors the local Windows credentials of the currently logged in user.  To connect to a remote database with Windows authentication, the user must login using a domain account.  This implies both computers are members of the same (or trusted) domain. 

Alternatively, you can specify a SQL login user id and password in the connection string instead of "Integrated Security=SSPI" so that user SQL authentication instead of Windows authentication.  SQL authentication will work in both local and remote scenarios as long as SQL authentication is allowed (see Papy's response). The script below shows how to create the SQL login, user and object permissions for this purpose.  You can also use the SSMS GUI for this task.

USE UGsimplify;
CREATE LOGIN MySqlLogin WITH PASSWORD = 'MyP@ssW0Rd', DEFAULT_DATABASE = UGsimplify;
CREATE USER MySqlLogin;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON DATABASE::UGsimplify TO MySqlLogin;

Note that the connection string in your application should be configurable anyway so it is a trivial task to use either Windows or SQL authentication depending on the installation scenario.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

thank you Dan, but I still cannot finished Pappy's suggest. I will try your suggest after finishing Pappy's first. But anyway is this a correct connection string in order to make it work?

stringkoneksi = @"Server = .\SQLEXPRESS;" +
                            "Database = UGsimplify;" +
                            "Integrated Security = false;" +
                            "User ID = 'sa';" +
                            "Password = 'blablabla'"

then where should I put your code above? Sorry for this but I'm a new guy in database and SQL.

Answer 8

Thank you Papy for your clear explanation, but when I try to implement your suggest I got stuck on this step "For that, doubleclick on your SQL Server instance name in SSMS.Double-click on the Security node, afterwards, double-click on the Connections node ( 1st node ) and right-click on the Connections node, click on the New Connection item and create your new SQL Server login" the problem is I can't find the connection node, could you help me deeply on this case? Thank you.

Answer 9

then where should I put your code above? Sorry for this but I'm a new guy in database and SQL.

This connection string is correct, although I suggest you don't use the sa login for routine application access.

The easiest way to run ad-hoc SQL scripts is using SSMS.  Just click the New Query button (which I see in your last screen shot), paste the script into the empty window and press F5 to execute.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

The easiest way to run ad-hoc SQL scripts is using SSMS.  Just click the New Query button (which I see in your last screen shot), paste the script into the empty window and press F5 to execute.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Oke I have just created a new Login and this is my new login properties

Is this what you mean by using another login account rather than sa?

Answer 11

The easiest way to run ad-hoc SQL scripts is using SSMS.  Just click the New Query button (which I see in your last screen shot), paste the script into the empty window and press F5 to execute.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

then after successfully created new login and logged on using windows authentication, I executed your code but I got something suspicious because I don't know what does it means on the message panel, here is the picture

Does it mean I have successfully implemented your custom sql authentication??

Answer 12

Does it mean I have successfully implemented your custom sql authentication??

The "already exists" errors refer to the CREATE LOGIN and CREATE USER statements so it seems those have already been created.  It looks like the GRANT statement executed successfully so you should be good.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 13

Does it mean I have successfully implemented your custom sql authentication??

The "already exists" errors refer to the CREATE LOGIN and CREATE USER statements so it seems those have already been created.  It looks like the GRANT statement executed successfully so you should be good.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Ok Dan, thank you so much your help so far,.

unfortunately I still cannot use my database in other computer, do you have any suggest for me maybe a step by step to make it able on different computer? I got really frustrated.