Adding a custom named instance to Windows Firewall exception list

We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.

Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?

You can add a firewall exception for the specific executable rather than the port number. Details are available in this KB Article,

Mike

Adding a custom named instance to Windows Firewall exception list

We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.

Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?

You can add a firewall exception for the specific executable rather than the port number. Details are available in this KB Article,

Mike

sql

Adding 2nd node fails

hi all ,
I tried several times to install sql clustering on
two sql nodes but when it tries to configure the resources on virtual server
after configuring sql server resource.
then I tried to configure it with single node it succeeded.
but when I want to add the 2nd node again it cannot configure the sql agent
and search resources on the 2nd node. if I remove the agent and search
resources from the first node and then add the 2nd node to the cluster it
runs.
but is this the way. should n't sql agent and search resouces be running on
a cluster?
the account I use is the local administrator on both machines. I followd
everything just as it was said in the microsoft documentations. what else
should i do? does it have a trick or what?
any idea whould be greatly appreciated.
When you say that you're using the local admin, do you mean that you're
using it for your SQL Server and SQL Agent services? You really need a
domain account for this and it should be part of the local admin group on
each node.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Alireza G." <csspcman@.hotmail.com> wrote in message
news:OO5X1FyjEHA.592@.TK2MSFTNGP11.phx.gbl...
hi all ,
I tried several times to install sql clustering on
two sql nodes but when it tries to configure the resources on virtual server
after configuring sql server resource.
then I tried to configure it with single node it succeeded.
but when I want to add the 2nd node again it cannot configure the sql agent
and search resources on the 2nd node. if I remove the agent and search
resources from the first node and then add the 2nd node to the cluster it
runs.
but is this the way. should n't sql agent and search resouces be running on
a cluster?
the account I use is the local administrator on both machines. I followd
everything just as it was said in the microsoft documentations. what else
should i do? does it have a trick or what?
any idea whould be greatly appreciated.
|||The SQLSTP.log on the setup node and the SQLSTPn.log (where highest value
for n is the most recent setup) from each node will contain the details of
the virtual SQL Server setup. Also check the last few entries in the
SQLCLSTR.log.
You can also try setting up a virtual SQL Server on one node, then re-run
the setup in maintenance mode and add the second node. Sometimes this
helps get past the virtual SQL Server creation setup.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name='''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> >
> > Hello All, I've got a script that will Create a Database and install the
> > tables onto a SQL server.
> > what code is used to add a user/username and password to allow the user
to
> > login?
> > I am aware that I can do this from the SQL Server, However, I've been
> > asked
> > to do it in the code.
> > I don't want to add a user to the network - just into 1 database. I
> > think
> > this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> >
> > I've looked at CreateUser. However when I try to run this I get an error
> > saying, "Could not find stored procedure 'createuser'"
> >
> >
> > Please help
> > TIA
> > Woody
> >
> >
>