DNN to Connect SQL Server with Window Authentication


The connection string is very simple:

<add name=SiteSqlServerconnectionString=Server=DBName1;Database=DNNDB;Trusted_Connection=Yes;/>

In order to use the above window authentication connection, you need to grant database access to Network Service account.

Here is the steps:

 

–1.Create a SQL Server login for the Network Service account.

 

— Sample code: exec sp_grantlogin ‘domainName\WebServerMachineName$’

 

–For marshdb1 to allow access from marshweb2

 

—-**** One time

 

–exec sp_grantlogin ‘INETU\myWEB2$’

 

—-****

 

–Target database

 

use DNNDB

 

go

 

 

 

–2.Create a database user in target database

 

exec sp_grantdbaccess ‘INETU\myWEB2$’

 

 

 

go

 

–Another way

 

–CREATE USER [INETU\myWEB2$] FOR LOGIN [INETU\myWEB2$]

 

–WITH DEFAULT_SCHEMA=[dbo]

 

–GO

 

–3.Place the database user in a database role.

 

EXEC sp_addrolemember‘db_owner’,[INETU\myWEB2$]

 

GO

 

 

 

–If not assigned to dbo, you  need to configure role’s permisson

 

–4.Grant permissions to the role. Ideally, you should grant execute permissions

 

— to selected stored procedures and provide no direct table access.

Just for your information, if you still want to use SQL Server authentication, you can refer to the following connection string:

<addname=SiteSqlServerconnectionString=Server=dbname;Database=DNNDB;uid=AuserName;pwd=auserPass;/>

 

 

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s