unable to connect to sql server using local sql authentication

Reason 1:

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

 

Reason 2: Create the user with the SQL Script:

— Create a new SQL Server Login

exec sp_addlogin @loginame = ‘MyUser’, @passwd = ‘P@ssw0rd’ — Grant the SQL login access to your database. — Create a database user called WebAppUser to which the login is associated Use YourDatabase GO exec sp_grantdbaccess ‘MyUser’ — Create a user-defined database role. exec sp_addrole ‘WebAppUserRole’ — Add the database user to the new database role. exec sp_addrolemember ‘WebAppUserRole’, ‘MyUser’ — Grant the role execute permissions on the stored procedure called sprocname grant execute on sprocname to WebAppUserRole

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