How do I add ASPNET user to SQLExpress DB?

I am going though the ASP.NET QuickStart tutorials. One of the lessons is to add a datagrid. I am able to do that with VWD and am able to connect to the database in VWD. However when I try to execute the application I get a "Login failure" message because the user pcName/ASPNET cannot login.

How do I add the ASPNET user to the SQLExpress database? There don't seem to be any administration tools that were installed with SQL Server Express 2005.

Thanks in advance for the help!

There are two permissions to SQL Server the Server permissions under security in Management in Management Studio and the new security section within the database. The management tools are a separate download. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||Thank you so much! It worked!|||

the link you posted is taking me to sqlexpress home page. And I don't see anything that is related to our discussion. Could you help little more and send me what I need to do in order to add aspnet user to sqlexpress??

thanks

Cemal

How Connect Sybase ASE 12.0 - OLE DB?

Hi:

How connect Microsoft Visual Studio BI with Sybase ASE 12.0 (not 12.5.x) database via OLE DB?

I try with "Srvr=,[PORT];Catalog=[DBNAME];User Id=[USER];Password=[PASSWORD];" but error

"Error en la conexión de prueba debido a un error al inicializar el proveedor. El proveedor 'Sybase.ASEOLEDBProvider.2' no está registrado en el equipo local."

Thanks

You need to have Sybase ASE OLEDB Provider installed in the machine where you are executing.

Thanks

sql

How connect SQL server 2000 with c#.net .....?

Hello masters,

help me yaar

i am using dotnet 2003 and SqL SERVER 2000. BUT UNABLE TO CONNECT DATABASE using dataAdaptor(design time ) or sqlconnection any can help me

gs

whats the error?

lets see the connection string.

make an empty text file called "c:\test.udl" - double click it and try to build a SQL Ole Db Provider datalink. . . can you get that to work?

Are the TCP protocols enabled in your machines client network settings and in the servers network settings?

|||Guess you don′t have the right connection string, look at www.connectionstrings.com for appropiate connection strings.

HTH, jens Suessmeyer.|||

hi i am using this code it dose not show any error as well as no output... please check it...

string conn = ("Data Source=localhost;"+

"Initial Catalog=gstest;"+

"User ID=sa;"+

"Password=logic;");

SqlConnection con = new SqlConnection();

con.ConnectionString = conn;

SqlDataAdapter da = new SqlDataAdapter("select * from tb1",con);

DataSet ds = new DataSet();

da.Fill(ds,"tb");

dataGrid1.DataSource = ds.DefaultViewManager;

|||

hello i got this error now,

what to do?

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

|||

If this is a web project (?!) you have to rebind the grid to the data

dataGrid1.DataBind()

HTH, jens Suessmeyer.

|||

what does this do:

try
{
DataSet ds = new DataSet();
string conn = "Data Source=localhost;Initial Catalog=gstest;User ID=sa;Password=logic;";
using(SqlConnection con = new SqlConnection(conn))
{
using(SqlDataAdapter da = new SqlDataAdapter("select * from tb1",con))
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

|||

hi master

it display...

Sql server dose not exist or access denied

but my sql server is running ......?

|||The using statement is doing garbage collection after the scope is funished. Fills your dataset like the first statement.

HTH, Jens Suessmeyer.|||

Jens Suessmeyer wrote:

Guess you don′t have the right connection string, look at www.connectionstrings.com for appropiate connection strings.

HTH, jens Suessmeyer.

the connection string is properly formed. whether that user/pw is correct and has permissions is another story.

Is the sql server on your development machine?

on the client, check that the TCP protocols are enabled using the Client Network Utility (cliconfig.exe) and on the server there is the Server Network Utility (svrnetcn.exe)

got windows firewall on? might try turning that off.

look at services control panel applet connected to the server.

does it show:

MSSQL or MSSQLSERVER

is it running?

|||Like it says "..or access denied". Whether you are not a default instance, then you have to suffix the instance name after the servername like localhost/instancename or you use a different port than the standard port then you have to add the port after the instancename servername/instance,Port ot you have a security problem, that the password is not valid for the sa account.

HTH, jens Suessmeyer.|||

still suffring

MSSQLSERVER is running on my pc i have also turn off the fire wall.

but the error is the same.

i like to know that after installing sql server is any process to configure with dotnet2003

if there is any pls inform me.....

pls.............

|||

Can you use integrated authentication ? If so (and you are in the adminstrative group, which should be server administrator by default int the installation) try using instead of UserID=yadayada;Password=blabla --> "Integrated Security=true"

HTH, Jens Suessmeyer-

|||

no, not really. . . did you check these:

Client Network Utility (cliconfig.exe) and Server Network Utility (svrnetcn.exe)

to make sure the tcp protocols are started?

did you make that empty udl file to check if Datalinks could connect?

|||

i am working on windows 2000 prof O/S

i have just downloaded the sqlserver 2000 form the net for installing this i have used the command in dos prompt...

C:\Program Files\Microsoft Visual Studio .NET 2003\Setup\MSDE\setup sapwd=logic securitymode=sql

after this sql server is installed in my computer and then i have downloded the Database manager(trial verson) to create the database and the tabels.

using this database and the tabels are created but

i am not able to connecte using dotnet2003

and pls tell me how to check the...

Client Network Utility (cliconfig.exe) and Server Network Utility (svrnetcn.exe)

to make sure the tcp protocols are started?

How come I can connect to a database through a datasource control, but not code?

Hi I am trying to open a database connection to a Northwind database. I can open it with a datasource control and return data to a gridview, but can't programically. I get a sqlexception error. this is ust for learning.

Protected Sub Button1_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.Click
Dim SAs New SqlConnection
Dim builderAs New SqlConnectionStringBuilder

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"
builder("Integrated Security") =True
builder("Connect Timeout") = 30
builder("User Instance") =True

S.ConnectionString = builder.ConnectionString

Me.Label1.Text = S.ConnectionString

S.Open()
Me.Label2.Text = S.State.ToString
S.Close()
Me.Label3.Text =Me.SqlDataSource1.ConnectionString.ToString

End Sub

The text in label2 and Label three are identical except there are "" around the datasource.

How come I can connect through the datasource control but not through code?

TallMike:

I get a sqlexception error.

What's the exception?|||

I pasted at the bottom of the reply error below. I tried removing the " " but get a syntax error at

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

Label1 reads

Data Source=".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True

Label2 reads "Closed"

Label3 reads

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True

I tried running this code but using the ASPNETDB database in my app_code folder, cause I thought it might be a permissions thing and I didn't set any permission after I downloaded Northwind from MS, but I got the same results

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

|||

Seems the connection string is not valid? Check the connectionString refering to 'SqlConnection.ConnectionString Property' topic in VS2005 Documentation.

FYI, here is a typical connection string:

"Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=Northwind;server=(local)"

|||

The connection string you mentioned is for a database on a server. I am trying to connect to a SQL Express database file. The first string in my example works the second doesn't. The only difference is the " "., but you can't use the connection builder without them.

Mike

|||These two ways work

S.ConnectionString ="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"S.ConnectionString = ConfigurationManager.ConnectionStrings(1).ToString

This way doesn't

builder(

"Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

builder(

"Integrated Security") =True

builder(

"Connect Timeout") = 30

builder(

"User Instance") =True

I give up

|||I've not used theSqlConnectionStringBuilder, but it seems to me that this one line:

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

should be 2:

builder("Data Source") =".\SQLEXPRESS"
builder("AttachDbFilename")="C:\SQL\NORTHWND.MDF"

sql

How can use 2 instance of SSRS?(WMI error code 80040219)

When I try to connect through SQL Server Management Studio,
the browser in the connect dialog can see the Reporting Services 2005 instance,
but when I try to connect, I get an error dialog with the message,
"An Unknown error has occured in WMI provider. error code 80040219."

it gives me no options for which instance to access
and connects to the default instance.
How do I connect to a 2nd named instance of SSRS via Management Studio?

i must use 2 different instance. (ex : 1) default instance 2) named instance) on the same physical server.
physical server os is cluster.
so i cant't remove one instance.

How can use 2 instance of SSRS via Management Studio?

Hi Jisungmon,

If you have installed on your machine named <Machine_Name> a second instance of Reporting Services named <Named_Instance>, you can connect to it using SQL Management Studio by following these steps:

- launch SQL Management Studio

- for Server type select: Reporting Services

- for Server name type: <Machine_Name>\<Named_Instance> ; or you can select <Browse for more ...>, expand the Reporting Services node, and select the same name from the list provided to you, then press OK

- for Authentication select what you prefer and eventually specify the parameters

- press Connect

Please let me know whether this is working for you.

Mihaela

This posting is provided "AS IS" with no warranties, and confers no rights.

How can one user view other users that belong to a database role?

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

The procedure relies on the security catalog views which are user sensitive, the query which is executed behind the scenes will bring back only the roles for the user.

select DbRole = g.name, MemberName = u.name, MemberSID = u.sid

from sys.database_principals u, sys.database_principals g, sys.database_role_members m

where g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id

order by 1, 2

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the post, however you've only explained why it's behaving as it does. I'm looking for advice on duplicating the Sql2000 behavior. For example, if I log on as user A I'd like to know if user B is a member of role ABC.|||Look what the Workbench does behind the scenes, it will execute the following commands:

set NOCOUNT OFF

CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)

declare @.generation int

set @.generation = 0

INSERT INTO #tmp_role_member_ids (id) SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@.generation

WHILE ( 1=1 )

BEGIN

INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)

SELECT a.member_principal_id, b.role_id, a.role_principal_id, @.generation + 1

FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b

ON a.role_principal_id = b.id

WHERE b.generation = @.generation

if @.@.ROWCOUNT <= 0

break

set @.generation = @.generation + 1

END

DELETE #tmp_role_member_ids WHERE id in (SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner') )

UPDATE #tmp_role_member_ids SET generation = 0;

INSERT INTO #tmp_role_member_ids (id, role_id, generation)

SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids

DELETE #tmp_role_member_ids WHERE generation = 0

SELECT

u.name AS [Name]

FROM

sys.database_principals AS rl

INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id

INNER JOIN sys.database_principals AS u ON u.principal_id = m.id

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

ORDER BY

[Name] ASC

drop table #tmp_role_member_ids

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

How can one user view other users that belong to a database role?

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

The procedure relies on the security catalog views which are user sensitive, the query which is executed behind the scenes will bring back only the roles for the user.

select DbRole = g.name, MemberName = u.name, MemberSID = u.sid

from sys.database_principals u, sys.database_principals g, sys.database_role_members m

where g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id

order by 1, 2

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the post, however you've only explained why it's behaving as it does. I'm looking for advice on duplicating the Sql2000 behavior. For example, if I log on as user A I'd like to know if user B is a member of role ABC.|||Look what the Workbench does behind the scenes, it will execute the following commands:

set NOCOUNT OFF

CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)

declare @.generation int

set @.generation = 0

INSERT INTO #tmp_role_member_ids (id) SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@.generation

WHILE ( 1=1 )

BEGIN

INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)

SELECT a.member_principal_id, b.role_id, a.role_principal_id, @.generation + 1

FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b

ON a.role_principal_id = b.id

WHERE b.generation = @.generation

if @.@.ROWCOUNT <= 0

break

set @.generation = @.generation + 1

END

DELETE #tmp_role_member_ids WHERE id in (SELECT

rl.principal_id AS [ID]

FROM

sys.database_principals AS rl

WHERE

(rl.type = 'R')and(rl.name=N'db_owner') )

UPDATE #tmp_role_member_ids SET generation = 0;

INSERT INTO #tmp_role_member_ids (id, role_id, generation)

SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids

DELETE #tmp_role_member_ids WHERE generation = 0

SELECT

u.name AS [Name]

FROM

sys.database_principals AS rl

INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id

INNER JOIN sys.database_principals AS u ON u.principal_id = m.id

WHERE

(rl.type = 'R')and(rl.name=N'db_owner')

ORDER BY

[Name] ASC

drop table #tmp_role_member_ids

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

How can one Connect (remotely) to SQLExpress which is installed on "Windows XP Home"

Hi all,

I have to deal with a private network of laptops which are all equiped with preinstalled "Windows XP home"

I've installed SQLExpress on one of these, but I cannot connect on it from the rest of the laptops. (I've successfuly done this with "Windows XP profesional" though, and local connections work fine as well).

I've already setup the SQLExpress to accept conections throuigh TCP/IP but I can not think of something else. (Security comes to mind, but I do not know how to resolve it on "Windows XP Home").

Thank you

Ioannis

Allow port 1433 and 1434 for TCP to come though as this should be blocked by the firewall by default. Try this first by disabling the firewall for a moment to see if this is the case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Ioannis,

Did opening the firewall solve this issue for you?

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Hi,

The firewall was already wide open!

What solved my problem was starting the "Browser" service.

Is this service supposed to be "down" by default?

thank you both for your kind responses

Ioannis

|||That is a question of settings in the setup during the installation, you can check the service to autimatically startup, if you don′t it will put it on Manual.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, this is by design.

SQL Express installs with the minimum external exposure possible. We also have remote connections disabled by default.

Mike

How can one Connect (remotely) to SQLExpress which is installed on "Windows XP Home&quo

Hi all,

I have to deal with a private network of laptops which are all equiped with preinstalled "Windows XP home"

I've installed SQLExpress on one of these, but I cannot connect on it from the rest of the laptops. (I've successfuly done this with "Windows XP profesional" though, and local connections work fine as well).

I've already setup the SQLExpress to accept conections throuigh TCP/IP but I can not think of something else. (Security comes to mind, but I do not know how to resolve it on "Windows XP Home").

Thank you

Ioannis

Allow port 1433 and 1434 for TCP to come though as this should be blocked by the firewall by default. Try this first by disabling the firewall for a moment to see if this is the case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Ioannis,

Did opening the firewall solve this issue for you?

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Hi,

The firewall was already wide open!

What solved my problem was starting the "Browser" service.

Is this service supposed to be "down" by default?

thank you both for your kind responses

Ioannis

|||That is a question of settings in the setup during the installation, you can check the service to autimatically startup, if you don′t it will put it on Manual.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, this is by design.

SQL Express installs with the minimum external exposure possible. We also have remote connections disabled by default.

Mike