Database problems

I am new to ASP.Net so I have a bunch of beginner problems here. My first BIG one is that every time I try and make a database connection in my code, I get

"Login failed for user 'sa'. Reason: Not associated with trusted SQL Server connection"

I know 'sa' is a valid user for the database, and I have retreived data other ways through swlDataAdapters, sqlConnections, and Datasets from design view. Any idea why I cannot expictitly connection to my DB in my code?? Or is there something stupid and small that I am overlooking?

Any help will be greatly appreciated.::Or is there something stupid and small that I am overlooking?

It is, but given the maount of people stumbling over it - you are not alone.

SQL Server simply is set not to look for the password but to use Windows integrated authentication ONLY. In this case access is checked against the account asp.net is running under, which has not enough security rights.

You need to reconfigure SQL Server to use mixed mode authentication, so that the username/password parts of the connection string actually have a real meaning. Especially the password part.

Database permissions

Hi,

In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!

So, I'm wondering what permissions on database files does ASP need? Which users need what permissions?

Thanks

The most reliable way to move a SQL Server database and permissions is Backup and Restore all other methods sometimes will not move permissions so you have to go in and add those as needed. There are two permissions in SQL Server needed to run Asp.net both are covered in the second thread and the first thread is a FAQ I created to help with moving SQL Server database from one computer to another. The permissions you give a user is at you discretion and that is covered under SQL Server object permissions in SQL Server BOL(books online). Hope this helps.

http://forums.asp.net/thread/1454694.aspx

http://forums.asp.net/thread/1492092.aspx

|||

Hi,

That's great thanks. The only problem is that my database is stored on a .mdf file and I cannot open it in MS SQL server management studio to change the database permissions. Is there a way around this?

Thanks

|||

That is not good practice so the SQL Server team have created a tutorial to help you connect your user instance to Management Studio when you do that you can go in and change the permissions. I would not advice you to give the everyone group full control but don't remove it from your Asp.net folders either because Asp.net is also a member of that group. Post again if you still have questions. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Database parameter in SQL Server 2005

Hello,

I'm try'n to create a check "IF" in SQL (sql server 2005) to a database parameter and not from the code (@.Attend for example)

But the thing is I don't know how to perfume a check "IF" with a database parameter I'm used to check parameters from the code like this:

IF(@.Attend = 1)

Begin

.

.

.

End

Select

But now I know it has to be in the Select but the question is how?

Thanks,

Nadin

The CASE method may work for you but that's just a guess based on the information you've given. If that doesn't work, you will have to provide more details on exactly what you want and an example of your data structure.

|||

It depends on what you are trying to do. You can do this:

IF @.Attend = 1 SELECT ...ELSE SELECT

Or you can do this:

SELECT CASE ( WHEN @.ATTEND=1 THEN'ATTEND' WHEN @.ATTEND=0 THEN'NO ATTEND' ELSE'UNKNOWN') As AttendText

Note that you only need the BEGIN and END if you have multiple statements in your if block, or if you like them for readability (just like the { and } in c++/c#)

Or you could even write a UDF to translate the number to a string if it's really complicated.