Last post Jun 14, 2012 12:17 PM by Rockstarter
Jun 13, 2012 12:40 PM|Rockstarter|LINK
In my web application I created a connection string to connect to a database on the same network. My computer is already part of the domain, Company\UserName, and using my Windows Authentication I can connect to the database via Sql Server Management Studio.
The server and database engine I'm connecting to is SERVER2\DATABASES, and the database I want is 'Website'.
On the server I created a new Login account called 'weblogin'. I gave it a password and turned the password enforcement off. I also assigned this Login 'Website' as the default database. The only server role I have selected for this Login is 'public',
and I have 'Website' mapped to User 'weblogin', with the Default Schema of 'dbo'... This Login has also been enabled for Login and granted permission to connect to the database engine.
Lastly, on the Database Engine properties, under Security, I have 'Server Authentication' set to 'Sql Server and Windows Authentication Mode'
So, the connection string I'm trying to use in my application is this:
Data Source=SERVER2\DATABASES;Initial Catalog=Website;User Id=weblogin;Password=********;Trusted_Connection=false;
I thought this was the right way to do everything but obviously I'm missing something. Can you offer up any suggestions?
Edit: If I set 'Trusted_Connection' to 'True' then I can connect, but I think that's because I'm logged in and it's authenticating me with Windows Authentication, which isn't what I want or need.
Jun 13, 2012 04:43 PM|ignatandrei|LINK
on the Database Engine properties, under Security, I have 'Server Authentication' set to 'Sql Server and Windows Authentication Mode'
did you restart sql server service?
Jun 14, 2012 04:10 AM|Decker Dong - MSFT|LINK
In fact I think you can just open VS——View=>Server Explorer and then try to create another connection to test whether it works properly or not，if it works successfully with SQL Server Account，just copy the connection string from the property panel into your
Jun 14, 2012 12:17 PM|Rockstarter|LINK
Thanks Ignatandrei. I set the settings but didn't realize I had to restart the service. When the network guys came in I had them restart it and I was able to access.
For anyone else reading... if you need to read/write, make sure your login has db_datareader/db_datawriter, else your login won't get to Read/Write From/To the database
Edit: Thanks to Decker for the tip on getting the connection string in the way he described.