SQL Server 2012 Express database connection string

Hello,

I have questions regarding the creation of the SQL Server 2012 Express edition database connection in ASP.NET.

I am not sure whether the 2 different methods I'm using to create the database connection is the correct & safe method or not.

The name of the database file I have is "NorthwindDBTelerikTesting1"

Method 1
In the web.config file at the root of my ASP.NET application, I write the connection string as shown below:

<connectionStrings>
    <add name="NorthwindSampleConnectionString" connectionString="Data Source=PCA2010\SQLEXPRESS;Initial Catalog=NorthwindDBTelerikTesting1;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Open in new window


then, in my ASP.NET pages, I write the codes shown below to connect to the SQL database:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindSampleConnectionString %>"

then write InsertCommand, SelectCommand etc.

Open in new window




Method 1
I create a new user (e.g. Richard) under the Security>Users> node of the SQL Server database with db_datareader and db_datawriter Database Role Membership as shown below.

SQL Server Management StudioThen in the web.config file at the root of my ASP.NET application, I write the connection string as shown below:

<connectionStrings>
    <add name="NorthwindSampleConnectionString" connectionString="Data Source=PCA2010\SQLEXPRESS;Initial Catalog=NorthwindDBTelerikTesting1;User ID=Richard;Password=SamplePassword" providerName="System.Data.SqlClient" />
  </connectionStrings>

Open in new window



===================

Is Method 2 more secure method than Method 1?
What does "Integrated Security=True" mean? Does it mean giving the website visitors viewing my ASP.NET pages the ability to see, modify and delete the data which the ASP.NET page displays?

Thank you.
ZKM128Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Johny BravoCommented:
When Integrated Security=True ,  the current Windows account credentials are used for authentication.
>> Does it mean giving the website visitors viewing my ASP.NET pages the ability to see, modify and delete the data which the ASP.NET page displays?
Completely wrong. It has nothing to do with Asp.Net pages, this setting is for connecting to database
0
ZKM128Author Commented:
So, which of the 2 methods that I have mentioned above should I use for displaying SQL Server data on ASP.NET pages and and also letting my website visitors to insert & modify the records (usually their personal detail records) into the SQL Server database using my ASP.NET pages?
What I'm asking is, should I create a user account for the SQL Server database and assign db_datareader & db_datawriter membership and include these login details when I create SQL server connection string in ASP.NET? Or should I simply type "Integrated Security=True" inside the SQL Server connection string and go ahead without creating/specifying any user account login details?
0
HuaMin ChenProblem resolverCommented:
Try to put this
        <connectionStrings>
                <add name="conn2" connectionString="Data Source=192.?.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
        </connectionStrings>

Open in new window


within Web.config/app.config file in your project, to ensure the project will work fine with the database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Johny BravoCommented:
Create a Sql User with db_Admin privilege and your second connection string is correct.
It is better to create a user and use that credentials in connectionstring than to use integrated security
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Create a Sql User with db_Admin privilege (...)
Don't give Admin privileges just because. This kind of privileges should be only for who really do the administration of the databases.

Your steps are all fine except the type of login that you added to the SQL Server.
If you want to use Windows Authentication then you need to add a domain user to the SQL Server instance. This is the more secure mode since it will be the Active Directory to send the credentials of the user to SQL Server and that's why you need to provide the parameter Integrated Security=True in your connection string. Means, use the current connected user credentials to login on SQL Server database.
If you want to use SQL Server authentication mode then you need to provide the username and password in the connection string, so won't be so secure because it will be visible for who can access the code. For this option you don't need to provide the parameter Integrated Security=True or if you want provide Integrated Security=False but that's the default value.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZKM128Author Commented:
Thank you. Completely understood. :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.