Dim connectionString As String = "server='srv_sql'; user id='sa'; password='MyPassword'; Database='MyDB'"
Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
However since I have
<identity impersonate=true>
in my web.config file... it tries to login to the MSDE database as the user.. not as the user SA.
I cannot change the web.config file, since I need that for the active directory authentication to work...
in SQL Server you can add users to a database through the enterprise manager.. how can I do a similar thing using MSDE ?
Is it even possible? or do I have to upgrade to a full SQL instance?
any help would be appreciatedTo answer your question, use the osql command line utility. See this KB article for more info:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.
what you need to do is use the sp_grantlogin system sp to add the login:
EXEC sp_grantlogin 'Corporate\Test'
Then give it access like this:
EXEC sp_grantdbaccess 'Corporate\BobJ', 'Bob'
You'll also need to give it permissions.
That said, there may need to be adjustments for use with Active Directory.
BUT, you should never, ever, NEVER use the sa login for database access. Not for any app and certainly not for an ASP.NET app. You're opening yourself up to a whole lot of hurt doing it this way. Instead, create a login that has only the specific permissions needed to run the app. No more.
It's more work, but you'll have made the app far more secure.
Don|||...for sure don't use SA on the page itself. What about permissioning the database with Windows authenticated logins since you are using AD...if you haven't already. If you do then you can set windows authentication via Internet Services Manager on the directory hosting the page (be sure to get rid of anonymous users)
